Sum w/ null values always equals null 2

[skicamel]

If need be, I'll post the whole script I'm working on, but an example of the issue is this:

select 1+2+3+null

This will give me 'Null' as the total. Is there some way to make SQL temporarily recognize nulls as zeroes, or just skip null values altogether when doing the math?

 

[fluteplr]

That is by design, since null does not mean zero it mean we don't know what the value is.

However, you can use the isnull function to do what you want.

isnull(myfield,0) returns the value of myfield unless it is null and then it returns 0.

So in your example

1+2+3+isnull(null,0) would = 6

 

[skicamel]

That's exactly what I was looking for. Thanks.