January 7, 2015 at 9:06 am
Mighty (1/7/2015)
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.
This is a bit funny in SQL.
1+2+3+NULL = NULL, but
1
2
3
+ NULL
------
6
January 7, 2015 at 9:18 am
Xavon (1/7/2015)
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...
Can you give an example to show how SUM is different than arithmetic addition and also how using isnull(<column>,0) with SUM gives you different results than just ignoring the nulls?
January 7, 2015 at 9:50 am
GROUPING was a bad choice for the question, so I'll award points back. My apologies for that. I've changed it.
Regarding COUNT, the * doesn't matter for NULLs as you're comparing the expression (COUNT(*)) to the data, which may have a NULL. To me, that's misplaced thinking.
Whether or not COUNT ignores NULLs depends on the expression, without a doubt. However that means that COUNT can ignore NULLs. I hesitated to use "can" since that usually causes all sorts of interpretation questions. In my mind, if you look at these, then you can't say that COUNT is always COUNT(col). It can be COUNT(*), which is often used. Therefore that's the answer (assuming GROUPING is removed).
As far as SUM(1+2+3+NULL), that's a separate debate. It's 6, though for SQL Server.
January 7, 2015 at 11:11 am
I think the use of the term "ignores" is not semantically correct (even if it used by BOL). "Excludes" is more accurate.
1 + 2 + 3 + NULL is NULL, absolutely. NULL is not replaceable by zero. Any set with a null value has a null sum, since null is "unknown" and therefore is free to range from the minimum to the maximum values of the domain. I have one full box of eggs, and another with an unknown number of eggs. How many eggs do I have?
Don Simpson
January 7, 2015 at 11:44 am
DonlSimpson (1/7/2015)
1 + 2 + 3 + NULL is NULL, absolutely. NULL is not replaceable by zero. Any set with a null value has a null sum, since null is "unknown" and therefore is free to range from the minimum to the maximum values of the domain. I have one full box of eggs, and another with an unknown number of eggs. How many eggs do I have?
Nope.
SELECT SUM(N)
FROM
(
SELECT N = 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT NULL
) tmp;
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 12:24 pm
Got the answer by deduction and removing the obvious wrongs ones, but I do not like the question.
COUNT, as any other aggregate, ignores NULL values.
COUNT(*) is special. The * means that it doesn't count values, but it count rows. A row can not be NULL (because a NULL is a scalar function, not a row). So for COUNT(*), it is hard to say whether NULLs are ignored or not, because there can be no NULLs in its input set.
To say that COUNT does not ignore NULL values, as the question implies, is flat out wrong.
January 7, 2015 at 2:06 pm
Koen Verbeeck (1/7/2015)
DonlSimpson (1/7/2015)
1 + 2 + 3 + NULL is NULL, absolutely. NULL is not replaceable by zero. Any set with a null value has a null sum, since null is "unknown" and therefore is free to range from the minimum to the maximum values of the domain. I have one full box of eggs, and another with an unknown number of eggs. How many eggs do I have?
Nope.
SELECT SUM(N)
FROM
(
SELECT N = 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT NULL
) tmp;
I was not referring to the T-SQL implementation, but to the "logical" problem. The T-SQL SUM() function excludes nulls from its process for the convenience of developers, but the logical sum of the complete set is null.
Don Simpson
January 8, 2015 at 12:07 am
Thank you for the post. Steve.
Orig: "Which of these aggregate functions does not ignore NULL values?"
Now: "Which of these aggregate functions does not exclude NULL values?"
When I loaded the Qtod, GROUPING was listed and when I selected it and submitted it was changed to MAX. I was thinking COUNT and GROUPING, but as the option button allows only one choice (so, considering the fact of COUNT(*) and COUNT(cName) usage- kind of felt like a trick and then I chose GROUPING.
Now I saw the complete conversation. 😎
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
January 8, 2015 at 1:27 am
Hugo Kornelis (1/7/2015)
Got the answer by deduction and removing the obvious wrongs ones, but I do not like the question.COUNT, as any other aggregate, ignores NULL values.
COUNT(*) is special. The * means that it doesn't count values, but it count rows. A row can not be NULL (because a NULL is a scalar function, not a row). So for COUNT(*), it is hard to say whether NULLs are ignored or not, because there can be no NULLs in its input set.
To say that COUNT does not ignore NULL values, as the question implies, is flat out wrong.
Thanks Hugo, you saved me some typing, as I was about to complain about the "null rows" as well 🙂
Best Regards,
Chris Büttner
January 8, 2015 at 3:30 am
Hugo Kornelis (1/7/2015)
Got the answer by deduction and removing the obvious wrongs ones, but I do not like the question.COUNT, as any other aggregate, ignores NULL values.
COUNT(*) is special. The * means that it doesn't count values, but it count rows. A row can not be NULL (because a NULL is a scalar function, not a row). So for COUNT(*), it is hard to say whether NULLs are ignored or not, because there can be no NULLs in its input set.
To say that COUNT does not ignore NULL values, as the question implies, is flat out wrong.
Thanks for the nice explanation
January 8, 2015 at 6:42 am
When I use a Group By it includes a total of any NULL values as well as the field I am grouping by.
January 8, 2015 at 7:04 am
marcia.j.wilson (1/7/2015)
Xavon (1/7/2015)
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...
Can you give an example to show how SUM is different than arithmetic addition and also how using isnull(<column>,0) with SUM gives you different results than just ignoring the nulls?
quick example of what I was thinking
DECLARE @NullSum TABLE (ID INT, Val1 INT, Val2 INT)
INSERT INTO @NullSum VALUES (1, 1, 2), (2, 3, 4), (3, 5, NULL)
DECLARE @Loop INT, @Val1 INT, @Val2 INT
SET @Val1 = 0
SET @Val2 = 0
SELECT @Loop = MIN(ID) FROM @NullSum
WHILE @Loop <= (SELECT MAX(ID) FROM @NullSum)
BEGIN
SET @Val1 = @Val1 + (SELECT Val1 FROM @NullSum WHERE ID = @Loop)
SET @Val2 = @Val2 + (SELECT Val2 FROM @NullSum WHERE ID = @Loop)
SET @Loop = ISNULL((SELECT MIN(ID) FROM @NullSum WHERE ID > @Loop), @Loop + 2)
END
SELECT SUM(Val1) AS Val1Sum, @Val1 AS Val1Add, SUM(Val2) AS Val2Sum, @Val2 AS Val2Add FROM @NullSum
SELECT Val1 + Val2 FROM @NullSum
SELECT SUM(Val1), SUM(Val2) FROM @NullSum
SELECT SUM(Val1) + SUM(Val2) FROM @NullSum
SELECT SUM(Val1 + Val2) FROM @NullSum
SELECT SUM(ISNULL(Val1, 0) + ISNULL(Val2, 0)) FROM @NullSum
January 8, 2015 at 7:28 pm
Ditto
The question asked if count would ignore values not ignore counting the row. I think the stated answer is wrong.
Max, in effect, handles Nulls as a lower possible value. It will return Null if there is no non-Null value.
January 9, 2015 at 7:03 am
jbwa (1/8/2015)
DittoThe question asked if count would ignore values not ignore counting the row. I think the stated answer is wrong.
Max, in effect, handles Nulls as a lower possible value. It will return Null if there is no non-Null value.
It will als return NULL if given an empty set:
CREATE TABLE dbo.TestIt (a int);
SELECT MAX(a) FROM dbo.TestIt;
Nulls are ignored; both an empty set and a set with only null values result in an empty set being actually processed by MAX, hence the NULL return.
SUM and AVG work the same, and I believe CHECKSUM_AGG does as well. COUNT will normally return 0 when the set fed into it has no data (either because the input set was empty, or because COUNT was asked to work on a nullable column and all input was ignored). I think I once saw a situation where COUNT could return NULL, but I do not remember the details, and I may be wrong.
January 16, 2015 at 12:54 pm
+1 thanks for posting this question.
Andre Ranieri
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply