NULL Aggregates

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720447

    Comments posted to this topic are about the item NULL Aggregates

  • Hany Helmy

    SSChampion

    Points: 13488

    Good back to basics question, thanx.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Nice question, but I'd argue GROUPING doesn't ignore NULL values as well.

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

  • manik_anu

    SSCrazy

    Points: 2367

    wow... +1.. nice question.. thanks...

    I had little remembrance of Count.

    Using SELECT COUNT(*) or SELECT COUNT(1) (which is what I prefer to use) will return the total of all records returned in the result set regardless of NULL values.

    Using COUNT(<Specific Column Name Here>)will count the number of non-NULL items in the specified column (NULL fields will be ignored).

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

  • Rune Bivrin

    SSCertifiable

    Points: 7829

    Not fond of this question.

    COUNT(*) counts NULL:s, but COUNT(<expression>) doesn't.

    select count(*), count(a)

    from (values (1), (NULL)) x(a)

    And GROUPING most certainly considers NULL:s.


    Just because you're right doesn't mean everybody else is wrong.

  • Carlo Romagnano

    SSC-Insane

    Points: 21987

    Rune Bivrin (1/7/2015)


    Not fond of this question.

    COUNT(*) counts NULL:s, but COUNT(<expression>) doesn't.

    select count(*), count(a)

    from (values (1), (NULL)) x(a)

    And GROUPING most certainly considers NULL:s.

    +1

    🙂

  • Vimal Lohani

    SSCommitted

    Points: 1650

    doubt on this question.

    Review once again.. :-):-):-)

    Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

  • Michael Riemer

    SSCertifiable

    Points: 5136

    I can see where all the doubt in this question arises - as discussed. Only saving grace is the statement in the Aggregate Function link, which states:

    Except for COUNT, aggregate functions ignore null values.

    .

    However even reading the information in the link for COUNT, you get the following:

    COUNT(*) returns the number of items in a group. This includes NULL values and duplicates.

    COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.

    COUNT(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values.

    My guess the problem then is that for all other aggregate functions, it isn't possible to ignore null at all, however with COUNT you have an option based on the execution of the query. Most of the remaining aggregate functions cant be used generically (with *) and so by design have to either include or exclude nulls. The default then is to ignore them.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Michael Riemer (1/7/2015)


    I can see where all the doubt in this question arises - as discussed. Only saving grace is the statement in the Aggregate Function link, which states:

    Except for COUNT, aggregate functions ignore null values.

    .

    That statement is incorrect, GROUPING does not ignore null values. It will return a value (0 or 1) even if there is a NULL in the source data.

    You can argue though if GROUPING is an aggregate function...

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

  • Neil Burton

    SSC-Insane

    Points: 22228

    Koen Verbeeck (1/7/2015)


    Nice question, but I'd argue GROUPING doesn't ignore NULL values as well.

    That's what I took from BOL and got the question wrong.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • This was removed by the editor as SPAM

  • vijaygodishala69

    SSC Rookie

    Points: 39

    Count(*) only count the NULL value but Count(<expression>) doesn't count the NULL value

  • P. K. Jat

    SSC Veteran

    Points: 236

    good question...

    but i have some doubt .

    i think count(*) and grouping both are not ignoring null values and count(exp) ignoring null values.

  • Ed Wagner

    SSC Guru

    Points: 286982

    I also clicked COUNT right away, then paused and thought about GROUPING. I actually looked it up and saw that I didn't ignore NULLs. Guessing between the two correct answers, I chose poorly. It's not a big deal, just debatable.

  • Xavon

    SSCrazy

    Points: 2280

    I wonder how MS is defining 'ignore'; because I would argue that if SUM ignored NULLs, I would not have to use half as many ISNULL(<column>, 0) statements.

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

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