January 7, 2015 at 6:28 am
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
January 7, 2015 at 6:32 am
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.
January 7, 2015 at 6:32 am
I too did not pick COUNT because COUNT(<expression>) ignores NULLs. That took some time getting used to.
January 7, 2015 at 6:37 am
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
January 7, 2015 at 6:41 am
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. 😉
January 7, 2015 at 7:06 am
I agree, GROUPING will count NULLs.
+1
January 7, 2015 at 7:11 am
I agree. GROUPING should be a correct answer as well.
January 7, 2015 at 7:17 am
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)
January 7, 2015 at 7:41 am
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
January 7, 2015 at 7:51 am
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/
January 7, 2015 at 7:53 am
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
January 7, 2015 at 8:12 am
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.
January 7, 2015 at 8:16 am
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.
January 7, 2015 at 8:31 am
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...
January 7, 2015 at 9:01 am
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 44 total)
You must be logged in to reply to this topic. Login to reply