Aggregates in T-SQL

  • Comments posted to this topic are about the item Aggregates in T-SQL

  • Good Question!

    And very rightly pointed out in the explanation that aggregate functions does not take NULL values into account. This is the reason you get the following warning when using aggregate functions with NULL values if ANSI_WARNINGS are enabled.

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

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • Good question indeed. I should have spotted that the aggregates will fail due to untyped NULL. If the null value in the last statement were typed, it would work just fine.



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • Easy question. I didn't even had to check to 3rd query, because the second one gave (NULL,0) and there was only one answer in the list with that option 🙂

    The explanation however is wrong as pointed out by Ole Kristian. This works perfectly:

    SELECT ISNULL(SUM(CONVERT(INT,NULL)),0) as sum_col1

    , ISNULL(COUNT(CONVERT(INT,NULL)),0) as cnt_col1

    FROM #TestAggregates

    Furthermore, the explanation doesn't explain the results of the other two queries. But I guess those are self-explanatory if you read the BOL pages on COUNT and SUM.

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

  • Thanks for the question.

    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

  • Koen Verbeeck (12/20/2012)


    Easy question. I didn't even had to check to 3rd query, because the second one gave (NULL,0) and there was only one answer in the list with that option 🙂

    The explanation however is wrong as pointed out by Ole Kristian. This works perfectly:

    SELECT ISNULL(SUM(CONVERT(INT,NULL)),0) as sum_col1

    , ISNULL(COUNT(CONVERT(INT,NULL)),0) as cnt_col1

    FROM #TestAggregates

    Furthermore, the explanation doesn't explain the results of the other two queries. But I guess those are self-explanatory if you read the BOL pages on COUNT and SUM.

    +1

  • There is just one variant of one aggregate function that DOES take nulls into account: COUNT(*).

    However, it is uncompatible with DISTINCT clause.

    SELECT COUNT(col1) as cnt_col1

    , COUNT(*) as cnt_asterisk

    FROM #testAggregates

    (8, 10)

  • Yggaz (12/21/2012)


    There is just one variant of one aggregate function that DOES take nulls into account: COUNT(*).

    However, it is uncompatible with DISTINCT clause.

    SELECT COUNT(col1) as cnt_col1

    , COUNT(*) as cnt_asterisk

    FROM #testAggregates

    (8, 10)

    I do not agree with this. COUNT(*) only considers counting the number of rows. It does not even bother to take a look at the columns and there values. Hence COUNT(*) is not the variant.

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • This was removed by the editor as SPAM

  • Regarding 3 rd query:-

    "Operand data type void type is invalid for sum operator."

  • Koen Verbeeck (12/20/2012)


    Easy question. I didn't even had to check to 3rd query, because the second one gave (NULL,0) and there was only one answer in the list with that option 🙂

    The explanation however is wrong as pointed out by Ole Kristian. This works perfectly:

    SELECT ISNULL(SUM(CONVERT(INT,NULL)),0) as sum_col1

    , ISNULL(COUNT(CONVERT(INT,NULL)),0) as cnt_col1

    FROM #TestAggregates

    Furthermore, the explanation doesn't explain the results of the other two queries. But I guess those are self-explanatory if you read the BOL pages on COUNT and SUM.

    +1

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • Lokesh Vij (12/21/2012)

    I do not agree with this. COUNT(*) only considers counting the number of rows. It does not even bother to take a look at the columns and there values. Hence COUNT(*) is not the variant.

    Terminological question. But looks like you are "more right" than me - your terminology is more convenient and contains "more" logic.

    But my position has some logic too. COUNT(*) does not bother to take a look at the columns => COUNT(*) takes into account any column values => COUNT(*) takes nulls into account.

    I repeat - in my opinion both of us are right, but you are "righter" :).

  • Yggaz (12/21/2012)


    Lokesh Vij (12/21/2012)

    I do not agree with this. COUNT(*) only considers counting the number of rows. It does not even bother to take a look at the columns and there values. Hence COUNT(*) is not the variant.

    Terminological question. But looks like you are "more right" than me - your terminology is more convenient and contains "more" logic.

    But my position has some logic too. COUNT(*) does not bother to take a look at the columns => COUNT(*) takes into account any column values => COUNT(*) takes nulls into account.

    I repeat - in my opinion both of us are right, but you are "righter" :).

    My friend..the point is not to prove each other write or wrong 🙂

    Point is having a healthy debate and learning out of that. If after excahnge of messages we have a good learning experience. .. there's nothing above that.

    Happy Learning:-)

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • Lokesh Vij (12/21/2012)


    Yggaz (12/21/2012)


    Lokesh Vij (12/21/2012)

    I do not agree with this. COUNT(*) only considers counting the number of rows. It does not even bother to take a look at the columns and there values. Hence COUNT(*) is not the variant.

    Terminological question. But looks like you are "more right" than me - your terminology is more convenient and contains "more" logic.

    But my position has some logic too. COUNT(*) does not bother to take a look at the columns => COUNT(*) takes into account any column values => COUNT(*) takes nulls into account.

    I repeat - in my opinion both of us are right, but you are "righter" :).

    My friend..the point is not to prove each other write or wrong 🙂

    Point is having a healthy debate and learning out of that. If after excahnge of messages we have a good learning experience. .. there's nothing above that.

    Happy Learning:-)

    Well Said Lokesh !!

    Here a reference and statement for learning

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

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

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

    http://msdn.microsoft.com/en-us/library/ms175997.aspx

    Yes , exactly copied and pasted...

    have a nice weekend !!!!

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • demonfox (12/21/2012)


    Well Said Lokesh !!

    Here a reference and statement for learning

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

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

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

    http://msdn.microsoft.com/en-us/library/ms175997.aspx

    Yes , exactly copied and pasted...

    have a nice weekend !!!!

    Thanks Demonfox....for making this learning experience even better 🙂

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

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

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