Basic COUNTs 1

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720887

    Comments posted to this topic are about the item Basic COUNTs 1

  • HappyGeek

    SSCoach

    Points: 18684

    Nice simple one to end the week on, thanks Steve.

    ...

  • manik_anu

    SSCrazy

    Points: 2367

    nice simple. added one condition in my mind while using count() if there is null value will not be calculated...๐Ÿ˜Ž๐Ÿ˜Ž

    Manik
    You cannot get to the top by sitting on your bottom.

  • paul s-306273

    SSChampion

    Points: 10615

    'For the DISTINCT keyword, null values are considered to be duplicates of each other. When DISTINCT is included in a SELECT statement, only one NULL is returned in the results, regardless of how many null values are encountered.'

    So I guessed 5.

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71907

    Nice, easy one to end the week on, thanks Steve

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    โ€œlibera tute vulgaris exโ€

  • gvoshol 73146

    Hall of Fame

    Points: 3190

    paul s-306273 - Friday, January 19, 2018 1:52 AM

    'For the DISTINCT keyword, null values are considered to be duplicates of each other. When DISTINCT is included in a SELECT statement, only one NULL is returned in the results, regardless of how many null values are encountered.'

    So I guessed 5.

    That's when selecting the values.  If you run this code

    SELECT DISTINCT charcol
    FROM dbo.TheCounts AS tc

    you will get 5 lines.

    But NULLS are not included in COUNT (nor in other aggregates, like MIN and MAX)

    Running the query in the question, you even get a message telling you:

    Warning: Null value is eliminated by an aggregate or other SET operation.

  • Data_Cat

    SSC Enthusiast

    Points: 154

    A question I could actually answer! However, after all of the other more difficult questions, I doubted myself. I admit I had to run the code to make sure I wasn't missing anything! Nice discussion about Nulls and how they act with Distinct. I also did not look at the messages tab to see the warning, so that is a good reminder to check that tab!

    Thanks!

  • Revenant

    SSC-Forever

    Points: 42467

    A coffee break question - thank you,. Steve!

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

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