问题描述:

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

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

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.