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 questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
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 questionsLink 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 questionsLink 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 questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply