January 16, 2016 at 8:04 pm
Comments posted to this topic are about the item Count Function Result (1)
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius
January 17, 2016 at 1:33 am
Good question to start the week with, thanx.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
January 18, 2016 at 1:59 am
Great question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 18, 2016 at 2:26 am
The "problem" with wanting to ask too much in a single question (and I can know, I have the same problem when submitting questions) is that readers can get at the correct answer by knowing just a subset. In my case, I knew what the result of all except the last two (with "ALL") would be. Turns out I actually needed to know only the first two to be left with only one answer that had to be correct.
The behaviour of COUNT is actually not that interesting at all. COUNT(expression) counts the number of rows for which the expression is not NULL. So COUNT(constant) is the same as COUNT(*) or COUNT(non-nullable column), and presents the number of rows. I personally have a great dislike for COUNT(constant) and/or COUNT(non-nullable column), as I think that COUNT(*) much better communicates to the future maintaiiner of the query that we are counting rows.
COUNT(NULL) would return 0 but it is actually invalid, but COUNT(CAST(NULL as int)) does indeed return 0. This for trivia purpose only, I really hope nobody ever uses this.
COUNT(nullable column) is in some cases needed, if we need to find the number of non-NULL values. Or COUNT(*) - COUNT(nullable column) will return the number of NULL values in the column.
Adding DISTINCT does exactly what it says on the package: it removes duplicates before counting. COUNT(DISTINCT constant) would return 0 for an empty table and 1 in all other cases, and is therefor just as useless as COUNT(NULL). COUNT(DISTINCT column) is a perfect way to find out how many different values there are in a column. Beware that this may be expensive on a non-indexed column, because SQL Server has to either sort the data or use a hash table to detect and remove duplicates - and those are expensive operators in the execution plan.
January 18, 2016 at 9:45 am
I didn't know the results of the last two either (COUNT ALL) because I never use it. I learned something new today - ALL is the default, which does exactly what it should whether it's included or not. Thanks.
January 18, 2016 at 12:17 pm
Got it wrong but found it amusing. Thanks, Gary!
January 24, 2016 at 11:58 am
Interesting question but an easy one , maybe a too easy one as I found the "good choice" only after the 2nd choice ( COUNT(100) is the same one as COUNT(*) and COUNT(column name) is returning the count of rows with the column "Name" value not null ).
See this link . In this old time , I was following this forum even I replied scarcely ( about 10 replies marked as answer )
I have looked at the 4 other proposals only for check.
For Hugo : you posts are long but always gripping ==> a real pleasure even if I have always to reread them at least 4 times ( because of my difficulties with the English language ) . So thank you very much.
February 25, 2016 at 4:18 pm
Thanks for the question.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply