Count Function Result (1)

  • chgn01

    Hall of Fame

    Points: 3526

    Comments posted to this topic are about the item Count Function Result (1)

    --------------------------------------
    ;-)“Everything has beauty, but not everyone sees it.” ― Confucius

  • Hany Helmy

    SSChampion

    Points: 13321

    Good question to start the week with, thanx.

  • Koen Verbeeck

    SSC Guru

    Points: 258927

    Great question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Ed Wagner

    SSC Guru

    Points: 286958

    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.

  • Revenant

    SSC-Forever

    Points: 42467

    Got it wrong but found it amusing. Thanks, Gary!

  • patricklambin

    SSCrazy Eights

    Points: 9964

    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.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the question.

Viewing 8 posts - 1 through 8 (of 8 total)

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