SQL adding numbers while handling nulls

Question

I have this statement here:

SELECT sum(table1.foo + table1.bar) AS Sum 
FROM table1
GROUP BY Fname;

When I try to add the numbers from foo and bar if one value from foo or bar is null it throws the numbers and gives me a different count sum

foo | bar
  6    4
  5    null
  9    1 
  2    1
  3    null

I want it to add all the numbers giving me a total of 31

but in this case it gives me a total of 23 Would love some help! Thanks!

mypetlion · Accepted Answer · 2017-10-25 21:07:01Z

2

Any Number + NULL = NULL. You want to indicate to the engine that when it sees a NULL, it should treat that NULL value as 0.

SELECT sum(ISNULL(table1.foo, 0) + ISNULL(table1.bar,0)) AS Sum 
FROM table1
GROUP BY Fname;

answered Oct 25, 2017 at 21:07

mypetlion

2,5445 gold badges21 silver badges22 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2017-10-25 21:06:12Z

1

Use coalesce():

SELECT sum(coalesce(table1.foo, 0) + coalesce(table1.bar, 0)) AS Sum 
FROM table1
GROUP BY Fname;

If you want the total, total on one row, remove the group by:

SELECT sum(coalesce(table1.foo, 0) + coalesce(table1.bar, 0)) AS Sum 
FROM table1;

answered Oct 25, 2017 at 21:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Tom · Accepted Answer · 2017-10-25 21:14:41Z

-1

I believe you'll need to have SQL replace nulls with a zero using "ISNULL".

Try: SELECT sum(ISNULL(fix_bat_sum.foo, 0) + ISNULL(fix_bat_sum.bar, 0) ) AS Sum

answered Oct 25, 2017 at 21:14

Tom

11 bronze badge

Isn't ISNULL sql server specific? Maybe COALESCE would be better idea.