Aggregates in T-SQL

  • venus.pvr

    SSCarpal Tunnel

    Points: 4813

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

  • Lokesh Vij

    SSChampion

    Points: 10836

    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

  • okbangas

    SSChampion

    Points: 11773

    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]

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    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

  • SQLRNNR

    SSC Guru

    Points: 281243

    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

  • Carlo Romagnano

    SSC-Insane

    Points: 21877

    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

  • Yggaz

    Mr or Mrs. 500

    Points: 555

    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)

  • Lokesh Vij

    SSChampion

    Points: 10836

    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

  • udayroy15

    Ten Centuries

    Points: 1076

    Regarding 3 rd query:-

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

  • demonfox

    SSCertifiable

    Points: 6289

    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:

  • Yggaz

    Mr or Mrs. 500

    Points: 555

    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" :).

  • Lokesh Vij

    SSChampion

    Points: 10836

    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

  • demonfox

    SSCertifiable

    Points: 6289

    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:

  • Lokesh Vij

    SSChampion

    Points: 10836

    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