Sum of null columns in SQL

Question

I have a table where A, B and C allow null values.

SELECT  
   A, B, C, 
   A + B + C AS 'SUM' 
FROM Table

If my A, B and C values are 10, NULL and NULL, my SUM column shows nothing in it.

Is there any way to fix this, and display SUM as 10? OTHER THAN converting NULL s to Zeros?

Answer 1

In this case you need to use IsNull(Column, 0) to ensure it is always 0 at minimum.

SELECT  
   A, B, C, 
   IsNull(A,0) + IsNull(B,0) + IsNull(C,0) AS 'SUM' 
FROM Table

ISNULL() determines what to do when you have a null value. if column returns a null value so you specified a 0 to be returned instead.

Answer 2

You could use SQL COALESCE which uses the value in the column, or an alternative value if the column is null

So

SUM ( COALESCE(A,0) + COALESCE(B,0) + COALESCE(C,0))