NULL Aggregates

  • Xavon (1/7/2015)


    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.

    I think 1 + 2 + 3 + NULL is the same as 1 + 2 + 3 + 0.

    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)


    Xavon (1/7/2015)


    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.

    I think 1 + 2 + 3 + NULL is the same as 1 + 2 + 3 + 0.

    But they aren't the same. The NULLs aren't included, so 1 + 2 + 3 + NULL would be the same as 1 + 2 + 3.

  • I too did not pick COUNT because COUNT(<expression>) ignores NULLs. That took some time getting used to.

  • Ed Wagner (1/7/2015)


    Koen Verbeeck (1/7/2015)


    Xavon (1/7/2015)


    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.

    I think 1 + 2 + 3 + NULL is the same as 1 + 2 + 3 + 0.

    But they aren't the same. The NULLs aren't included, so 1 + 2 + 3 + NULL would be the same as 1 + 2 + 3.

    The result is the same. Both times 6.

    My point was that if you replace NULL with 0 or not, it doesn't matter when using SUM.

    (and 1 + 2 + 3 is the same as 1 + 2 + 3 + 0 :-P)

    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)


    Ed Wagner (1/7/2015)


    Koen Verbeeck (1/7/2015)


    Xavon (1/7/2015)


    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.

    I think 1 + 2 + 3 + NULL is the same as 1 + 2 + 3 + 0.

    But they aren't the same. The NULLs aren't included, so 1 + 2 + 3 + NULL would be the same as 1 + 2 + 3.

    The result is the same. Both times 6.

    My point was that if you replace NULL with 0 or not, it doesn't matter when using SUM.

    (and 1 + 2 + 3 is the same as 1 + 2 + 3 + 0 :-P)

    The point is correct, so the ISNULL(column, 0) isn't necessary unless MS plans to revise the SUM function to require it. πŸ˜‰

  • I agree, GROUPING will count NULLs.

    +1

  • I agree. GROUPING should be a correct answer as well.

  • I chose GROUPING as well, as Count only includes NULL values for the special case of Count(*):

    SELECT

    Count(*),

    Count(MyValue),

    FROM

    (

    VALUES (1), (2), (3), (NULL)

    ) AS X (MyValue)

  • COUNT isn't the only one, because COUNT_BIG behaves the same way as COUNT. Also, like others who have already commented, I find it hard to see how it is in any sense reasonable to claim that a function whose whole raison d'Γͺtre is to classify NULLs (GROUPING) can be said to ignore nulls. And the explanation goes wrong when it claims that using distinct is the only way of making COUNT ignore nulls, since it ignores nulls when given any argument other than "*".

    Of course BOL is broken too, as demonstrated by the reference in the explanation.

    Tom

  • Maybe the question should have been worded like this:

    According to the first paragraph about aggregates in Books Online, which of these aggregate functions does not ignore NULL values?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Ed Wagner (1/7/2015)


    Koen Verbeeck (1/7/2015)


    Xavon (1/7/2015)


    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.

    I think 1 + 2 + 3 + NULL is the same as 1 + 2 + 3 + 0.

    But they aren't the same. The NULLs aren't included, so 1 + 2 + 3 + NULL would be the same as 1 + 2 + 3.

    And for me 1+2+3+NULL is the same as 1+2+3+256

    But sometimes it is the same as 1+2+3+(-8)

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

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

    Couldn't agree more. Grouping identifies where a null value came into play whenever using operators like rollup and cube. I don't really think of that as ignoring.

  • Hi,

    Count does not ignore NULLs values if we use it as follow : COUNT(*), but if we write COUNT(ColumnName) in this case Count ignores Nulls Values.

    I think GROUPING does not ignore nulls values ?

    Thanks.

  • Ed Wagner (1/7/2015)


    Koen Verbeeck (1/7/2015)


    Ed Wagner (1/7/2015)


    Koen Verbeeck (1/7/2015)


    Xavon (1/7/2015)


    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.

    I think 1 + 2 + 3 + NULL is the same as 1 + 2 + 3 + 0.

    But they aren't the same. The NULLs aren't included, so 1 + 2 + 3 + NULL would be the same as 1 + 2 + 3.

    The result is the same. Both times 6.

    My point was that if you replace NULL with 0 or not, it doesn't matter when using SUM.

    (and 1 + 2 + 3 is the same as 1 + 2 + 3 + 0 :-P)

    The point is correct, so the ISNULL(column, 0) isn't necessary unless MS plans to revise the SUM function to require it. πŸ˜‰

    The real lesson is don't comment before your coffee, or your brain might decide that SUM is the same as arithmetic addition...

  • I choose GROUPING, and still think it is the correct answer.

    As others already said COUNT only doesn't ignore NULLs when used with the *. But * is not a value, so it can't be NULL.

    I think 1 + 2 + 3 + NULL is the same as 1 + 2 + 3 + 0.

    In relation to this, I don't fully agree. For me 1 + 2 + 3 + NULL should return NULL, the same as that 'string1' + 'string2' + 'string3' + NULL returns NULL.

Viewing 15 posts - 16 through 30 (of 45 total)

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