SQL Server Performance Forum – Threads Archive
How is better to get SUM OF COUNTs
HiI am looking for some better solutions to do this action:
I want to get the sum of some Counts which are grouped.
It means that I want to calculate the sum of a column of some records that this column is made with the COUNT aggregate function:
SELECT SUM(CITY_COUNT)
FROM (SELECT COUNT(CITY) CITY_COUNT
FROM MYTABLE
GROUP BY CREATION_DATE)
But the number of records are too much, so this solution may not have a good performance. what’s your idea?
regards
Check out if the ROLLUP operator will help you. [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
what about Select Count(CITY ) from MyTable where CITY in (‘A’,’B’,’C’) —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/
The original query looks a little funny: get the number of entries (not null) on the CITY column, for each CREATION_DATE, then ignore the CREATION_DATE altogether and give the total count … You can use COUNT(CITY) to ignore nulls … SELECT COUNT(CITY)
FROM MYTABLE … but if there are zero-length string entries on the CITY column then these will be counted as well.
To remedy that, use this: SELECT COUNT(*)
FROM MYTABLE
WHERE CITY <> ”
This might help.
http://toponewithties.blogspot.com/2004/08/let-us-count-them.html Roji. P. Thomas
http://toponewithties.blogspot.com
]]>