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?

PaulG · Accepted Answer · 2014-04-26 10:29:56Z

6

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

answered Apr 26, 2014 at 10:29

PaulG

14.1k9 gold badges61 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

@MitchWheat Not in the database it isn't.

Neither marc or I were ever suggesting converting the actual data!

@MitchWheat Well, sorry, but I didn't know how to get my query to calculate the sum. Hence the question.

Saurabh · Accepted Answer · 2014-04-26 10:32:14Z

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.

answered Apr 26, 2014 at 10:32

Saurabh

73.8k44 gold badges193 silver badges251 bronze badges