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

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. — Matt Ball
– Matt Ball, Commented Oct 30, 2012 at 3:32
@MattBall Yes! I see now, the calculation skips any row that has a null column1 or column2. If you add that as an answer, I'll mark it as correct — Gary Barrett
– Gary Barrett, Commented Oct 30, 2012 at 3:50

John Woo · Accepted Answer · 2012-10-30 03:42:30Z

7

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

edited Oct 30, 2012 at 3:42

answered Oct 30, 2012 at 3:32

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 10:29:49Z

4

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)

edited May 23, 2017 at 10:29

CommunityBot

11 silver badge

answered Oct 30, 2012 at 3:52

Matt Ball

361k102 gold badges655 silver badges725 bronze badges

1 Comment

Gary Barrett Over a year ago

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

Karl · Accepted Answer · 2012-10-30 03:35:12Z

3

Because it is adding value +NULL before summing

Try sum(column1) + sum(column2)

answered Oct 30, 2012 at 3:35

Karl

3,38223 silver badges27 bronze badges

1 Comment

Andriy M Over a year ago

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.

Vikdor · Accepted Answer · 2012-10-30 03:32:38Z

1

Use the ISNULL function to get the desired behavior:

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

answered Oct 30, 2012 at 3:32

Vikdor

24.2k10 gold badges66 silver badges86 bronze badges

Comments

Vishal Patel · Accepted Answer · 2012-10-30 03:34:13Z

1

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

answered Oct 30, 2012 at 3:34

Vishal Patel

1301 gold badge2 silver badges10 bronze badges

Comments

Han · Accepted Answer · 2012-10-30 03:52:47Z

1

number + NULL = NULL
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.

That's reason why your result is 9. ISNULL(expression, replacement_value) can help you in this situation. :)

answered Oct 30, 2012 at 3:52

Han

3,3333 gold badges28 silver badges42 bronze badges

Comments

Andriy M · Accepted Answer · 2012-10-30 07:51:00Z

1

An alternative explanation (just in case it works better for someone):

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.

answered Oct 30, 2012 at 7:51

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Collectives™ on Stack Overflow

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

7 Answers 7

Comments

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related