问题描述:

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.

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.

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):

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.

相关阅读:
Top