Table containing the following values:
The following query:
SUM([Column1] + [Column2] )
returns a value of
9 when it should be returning
21. Why? How does it arrive at the value?
I know the SUM can be corrected by adding
ISNULL like so:
SUM(ISNULL([Column1], 0) + ISNULL([Column2], 0))
but I would like to know the logic behind the value
What is the sum of
null and a number, exactly? Note where the
9 comes from: the only row which has non-null
One viable solution has of course already been posted. But then, where's the fun in jumping right to the fix?
(copypasta'd at OP's request)
COALESCE to convert null into
0. (that's it if you want null values as zero.)
SELECT SUM(COALESCE(column1,0) + COALESCE(column2,0)) FROM table1
Because it is adding value +NULL before summing
Try sum(column1) + sum(column2)
Use the ISNULL function to get the desired behavior:
SELECT SUM(ISNULL(Column1, 0) + ISNULL(Column2, 0)) FROM [myTable]
Its a problem with a Null value.
SELECT SUM(IsNull(Column1, 0) + IsNull(Column2, 0) ) FROM [myTable]
to ensure it is always 0 at minimum.
That's reason why your result is 9. ISNULL(expression, replacement_value) can help you in this situation. :)
An alternative explanation (just in case it works better for someone):
+ but do not affect
SUM(): where a NULL is
+-ed, it evaluates to NULL, where it is SUMmed, it is ignored. (But
SUM() can return NULL when not a single argument was a value.)
So, there is only one row in your data sample (the last one) that produces a non-NULL result for the
+, and that result is
9, which is what
SUM() returns as well.