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

Table containing the following values:

``Column1 Column21 NULLNULL 42 NULLNULL 53 6``

The following query:

``SELECTSUM([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:

``SELECTSUM(ISNULL([Column1], 0) + ISNULL([Column2], 0))FROM [myTable]``

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

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)

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

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.

Thank you

1. number + NULL = NULL
2. SUM(expression) function is manipulated by calculating the expression by each row separately and return a NOT_NULL value. And then all the results will be summed and returned.

NULLs affect `+` 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.