This should be easy... - Using NULL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

This should be easy... - Using NULL

Author

Topic

natg504
Starting Member

14 Posts

Posted - 2009-02-23 : 11:26:47

I have a table with data similar to this format:

clm1 clm2 clm3 clm4
10 20 30 40
50 60 70 NULL
80 90 NULL 100

My SQL is something like this:
SELECT (clm1 + clm2 + clm3 + clm4) AS TotalClms

I'd like the result to be (ignoring NULLs):

TotalClms
100
180
170

But instead I get:
TotalClms
100
NULL
NULL

How can I fix this?

Thanks!

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-02-23 : 11:35:57

SELECT (isnull(clm1, 0) + isnull(clm2, 0) + isnull(clm3, 0) + isnull(clm4, 0)) AS TotalClms

(assuming you added the third row incorrectly)