My Select SUM query returns null. It should return 0

Question

I'm trying to sum up Customer balances using the following query:

select sum(balance) from mytable where customer = 'john'  

However, if the customer has no balance (i.e. no matching rows in the mytable table), my query returns null and not 0. What's the problem?

Answer 1

Try this:

select COALESCE(sum(balance),0) from mytable where customer = 'john'  

This should do the work. The coalesce method should return the 0.

Answer 2

That's not a problem. If there are no rows, sum() will return null. It will also return null if all rows have a null balance.

To return zero instead, try:

select isnull(sum(balance),0) from mytable where customer = 'john'