get count from count query

Question

get count from count query

audracmckay

SSC Rookie

Points: 33
More actions
June 9, 2010 at 5:06 pm

#222002

I have this query which I am using for an asp report app I made:
SELECT somecolumn
COUNT(somecolumn) AS NumOccurrences
FROM sometable
GROUP BY somecolumn
HAVING ( COUNT(somecolumn) >= 0 )
This returns results something like this:
somecolumn, numoccurrences
value1, 5
value2, 4
value3, 2
I need to do two things:
1- I need to be able to add up all the numoccurrences and return that (in this case, the value would be 11)
2- I need to be able to just count up the number of rows returned (in this case, the value would be 3)
So this might be a silly question, but can I do a select statement for count or sum on my existing select statement? Is there a better way to do that? 🙂

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Looks like the query is working, is there something that it is not doing that you would like it to do?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

audracmckay SSC Rookie Points: 33 More actions · Answer 2

Sorry, I accidentally posted before I was finished. First time in the forum 🙂

audracmckay SSC Rookie Points: 33 More actions · Answer 3

Now I'm done with my question (I edited my post). Thanks!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Are you truly on SQL 2000?

Here is a solution for SQL 2005 (sorry just noticed the forum group after having worked the solution - will post a sql 2000 solution in a bit)

--Your query

SELECT Table_Name

,COUNT(Table_Name) AS NumOccurrences

FROM INFORMATION_SCHEMA.Columns

GROUP BY Table_Name

HAVING ( COUNT(Table_Name) >= 0 );

--query to meet requirements

With CounterCTE as (

SELECT Table_Name

,COUNT(Table_Name) AS NumOccurrences

FROM INFORMATION_SCHEMA.Columns

GROUP BY Table_Name

HAVING ( COUNT(Table_Name) >= 0 )

)

Select Count(Table_Name) as ColCount, Sum(NumOccurrences) as TotalOccurrences

From CounterCte

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Here is a SQL 2000 version

Declare @CounterTable Table (Table_Name Varchar(50), NumOccurrences int)

Insert into @CounterTable (Table_Name, NumOccurrences)

SELECT Table_Name

,COUNT(Table_Name) AS NumOccurrences

FROM INFORMATION_SCHEMA.Columns

GROUP BY Table_Name

HAVING ( COUNT(Table_Name) >= 0 )

Select Count(Table_Name) as ColCount, Sum(NumOccurrences) as TotalOccurrences

From @CounterTable

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

audracmckay SSC Rookie Points: 33 More actions · Answer 6

hmm. no I'm on 2005 too. Sorry. I guess i didn't notice that either!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

audracmckay (6/9/2010)
hmm. no I'm on 2005 too. Sorry. I guess i didn't notice that either!

That's all good. Solutions for both types are posted. The SQL 2000 version will work on 2005 as well.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

audracmckay SSC Rookie Points: 33 More actions · Answer 8

audracmckay

SSC Rookie

Points: 33

June 9, 2010 at 5:32 pm

#1178373

That worked beautifully!! Thanks so much!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

WayneS SSC Guru Points: 95460 More actions · Answer 10

CirquedeSQLeil (6/9/2010)
Here is a SQL 2000 version
Declare @CounterTable Table (Table_Name Varchar(50), NumOccurrences int)
Insert into @CounterTable (Table_Name, NumOccurrences)
SELECT Table_Name
,COUNT(Table_Name) AS NumOccurrences
FROM INFORMATION_SCHEMA.Columns
GROUP BY Table_Name
HAVING ( COUNT(Table_Name) >= 0 )
Select Count(Table_Name) as ColCount, Sum(NumOccurrences) as TotalOccurrences
From @CounterTable

You can eliminate the table variable, and do this as a sub-query:

SELECT Count(Table_Name) as ColCount, Sum(NumOccurrences) as TotalOccurrences

FROM (SELECT Table_Name,

COUNT(Table_Name) AS NumOccurrences

FROM INFORMATION_SCHEMA.Columns

GROUP BY Table_Name

HAVING ( COUNT(Table_Name) >= 0 )) TableAlias

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Thanks Wayne.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

As a sidebar and extension of the problem... lookup WITH ROLLUP and WITH CUBE as associated with GROUP BY. Powerful tools there. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andrew Terwiel-314315 SSC Enthusiast Points: 104 More actions · Answer 13

You can use a derived table. Like this:

SELECT

SUM(DerivedTableName.NumOfOccurences) SumOccurences

, COUNT(DerivedTableName.NumOfOccurences) CountOccurences

FROM

(SELECT somecolumn

COUNT(somecolumn) AS NumOccurrences

FROM sometable

GROUP BY somecolumn

HAVING ( COUNT(somecolumn) >= 0 )

) DerivedTableName