CODEWITHSUNDEEP
sql
Sleepyfalcon
Sleepyfalcon

Reputation: 35

SQL adding numbers while handling nulls

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!

Upvotes: 0

Views: 2765

Answers (3)

Tom
Tom

Reputation: 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

Upvotes: -1

mypetlion
mypetlion

Reputation: 2524

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;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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;

Upvotes: 1

Related Questions