NULL Aggregates

  • Comments posted to this topic are about the item NULL Aggregates

  • Good back to basics question, thanx.

  • 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

  • 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.

  • 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.

  • 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

    🙂

  • 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

  • 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.

  • 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

  • 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

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

  • good question...

    but i have some doubt .

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

  • 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.

  • 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