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

Question

A 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 9 when it should return 21 . What for? How does it reach value?

I know that SUM can be fixed by adding ISNULL like this:

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

but I would like to know the logic of value 9

Answer 1

What is the sum of null and numbers, exactly? Notice where 9 happens: the only row that has non-empty Column1 and Column2 .

One viable solution , of course, has already been published. But then, where is the fun of jumping straight to the fix?

(copypasta'd at OP request)

Answer 2

use COALESCE to convert null to 0 . (this is if you want null values ​​to be zero.)

 SELECT SUM(COALESCE(column1,0) + COALESCE(column2,0)) FROM table1 

• See SQLFIDDLE Demo

Answer 3

Because it adds + NULL before summing

Try sum (column1) + sum (column2)

Answer 4

Use the ISNULL function to obtain the desired behavior:

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

Answer 5

This is a null value problem.

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

to ensure that it is always 0.

thanks

Answer 6

• number + NULL = NULL
• The SUM function ( expression ) is processed by evaluating the expression each line separately and returns the value NOT_NULL . And then all the results will be summarized and returned.

For this reason, your result is 9. ISNULL (expression, replacement_value) can help you in this situation. :)

Answer 7

Alternative explanation (just in case, this is better for someone):

NULLs affect + , but do not affect SUM() : where NULL + -ed, it evaluates to NULL, where it is SUMmed, it is ignored. (But SUM() can return NULL if no argument was a value.)

So in your sample data (last) there is only one row that produces a non-NULL result for + , and this result is 9 , which also returns SUM() .