Why does Sql Sum of multiple columns containing nulls return incorrect result?

Question

Table containing the following values:

Column1  Column2
1        NULL
NULL     4
2        NULL
NULL     5
3        6

The following query:

SELECT 
    SUM([Column1] + [Column2] ) 
FROM [myTable]

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:

SELECT 
    SUM(ISNULL([Column1], 0) + ISNULL([Column2], 0)) 
FROM [myTable]

but I would like to know the logic behind the value 9

Solution 1

What is the sum of null and a number, exactly? Note where the 9 comes from: the only row which has non-null Column1 and Column2.

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)

Solution 2

use 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

• See SQLFIDDLE Demo

Solution 3

Because it is adding value +NULL before summing

Try sum(column1) + sum(column2)

Solution 4

Use the ISNULL function to get the desired behavior:

SELECT SUM(ISNULL(Column1, 0) + ISNULL(Column2, 0)) FROM [myTable]

Solution 5

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.

Thank you

View more solutions

Comments

[Andriy M]

This is a nice alternative to the ISNULL/COALESCE suggestions by others. Note, however, that if one column had no values at all (only NULLs), this would evaluate to NULL. I would still prefer ISNULLing the result of SUM than its argument(s), though, so +1 from me.

[Gary Barrett]

I marked this as the answer as it was the first to explain the logic of where the 9 comes from.