Count Function Result (2)

  • chgn01

    Hall of Fame

    Points: 3507

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

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

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Good coverage of the count function with two QotDs. Thanks.

    Igor Micev,
    My blog: www.igormicev.com

  • This was removed by the editor as SPAM

  • Toreador

    SSChampion

    Points: 11223

    Again, a good idea, but the choices available meant I only had to think about the first two before I got a unique option.

  • TomThomson

    SSC Guru

    Points: 104755

    Toreador (1/25/2016)


    Again, a good idea, but the choices available meant I only had to think about the first two before I got a unique option.

    Yes, if you know how COUNT works you don't have to check all the numbers.

    But you don't have to know much about COUNT to get the answer. The where clause means you only have two rows to look at: (140, NULL) and (150,NULL). So any answer containing 3 is wrong. There's no imaginable way any count function could return for 1 for COUNT('Aim') when there are only thse two rows. So that leaves only two possible answers, and just knowing that that COUNT(column) doesn't count nulls tells you which one is right. So all you have to know about COUNT to get this answer is that COUNT(column) doesn't count NULLs.

    Tom

  • Iulian -207023

    SSCertifiable

    Points: 7507

    OK, so we have :

    COUNT( constant ) is the number of records returned by the query

    COUNT( DISTINCT ... ) is self explanatory

    COUNT( ALL ... ) is the default and is opposed to DISTINCT

    COUNT( NULL ) is zero; although when I query: SELECT COUNT( NULL ) it returns the error:

    Msg 8117, Level 16, State 1, Line 42

    Operand data type NULL is invalid for count operator.

    Any idea why this ?

    T.a.

  • Ed Wagner

    SSC Guru

    Points: 286955

    Iulian -207023 (1/25/2016)


    OK, so we have :

    COUNT( constant ) is the number of records returned by the query

    COUNT( DISTINCT ... ) is self explanatory

    COUNT( ALL ... ) is the default and is opposed to DISTINCT

    COUNT( NULL ) is zero; although when I query: SELECT COUNT( NULL ) it returns the error:

    Msg 8117, Level 16, State 1, Line 42

    Operand data type NULL is invalid for count operator.

    Any idea why this ?

    T.a.

    Interesting. I half-expected this to be covered in Books Online, but it isn't. My guess is that it's because NULL is unknown. If you wanted to count the nulls, you'd need to include a predicate in the WHERE clause.

  • Toreador

    SSChampion

    Points: 11223

    Ed Wagner (1/25/2016)[/b]


    Interesting. I half-expected this to be covered in Books Online, but it isn't. My guess is that it's because NULL is unknown. If you wanted to count the nulls, you'd need to include a predicate in the WHERE clause.

    It's complaining about the data type rather than the value. This works (returns 0)

    SELECT COUNT( CAST(NULL AS INT))

  • Ed Wagner

    SSC Guru

    Points: 286955

    Toreador (1/25/2016)


    Ed Wagner (1/25/2016)[/b]


    Interesting. I half-expected this to be covered in Books Online, but it isn't. My guess is that it's because NULL is unknown. If you wanted to count the nulls, you'd need to include a predicate in the WHERE clause.

    It's complaining about the data type rather than the value. This works (returns 0)

    SELECT COUNT( CAST(NULL AS INT))

    Right - it's unknown.

  • Revenant

    SSC-Forever

    Points: 42468

    Ed Wagner (1/25/2016)


    Toreador (1/25/2016)


    Ed Wagner (1/25/2016)[/b]


    Interesting. I half-expected this to be covered in Books Online, but it isn't. My guess is that it's because NULL is unknown. If you wanted to count the nulls, you'd need to include a predicate in the WHERE clause.

    It's complaining about the data type rather than the value. This works (returns 0)

    SELECT COUNT( CAST(NULL AS INT))

    Right - it's unknown.

    Or not applicable.

    Gary, thanks for an interesting QotD!

  • patricklambin

    SSCrazy Eights

    Points: 9964

    Thanks Gary for this nice QOD even if I have hesitated during 15 minutes because I detected the 3rd choice as the good one but I was thinking of a trap as it was too evident ( according to me ).

  • twin.devil

    SSC-Insane

    Points: 22208

    Good question. Thanks for sharing Gary.

    Because Count(-100) and Count(1) are the same thing doesn't it. 🙂

  • Stephen Grimshaw

    Mr or Mrs. 500

    Points: 573

    I'm a bit surpised at the behaviour when a number appears in the brackets. Wasn't it once the case that a number would have been interpreted as colid?

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Stephen Grimshaw (1/26/2016)


    I'm a bit surpised at the behaviour when a number appears in the brackets. Wasn't it once the case that a number would have been interpreted as colid?

    No, that has never been the case.

    I think you are confused by the (supported but not recommended) syntax of ORDER BY where a positive integer is interpreted as a column ordinal.


    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/

  • Koen Verbeeck

    SSC Guru

    Points: 258892

    Nice question, thanks.

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

Viewing 15 posts - 1 through 15 (of 16 total)

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