My Select SUM query returns null. It should return 0

Solution 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.

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'

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

Maybe you are thinking of COUNT's behaviours?

COUNT(Field) will return 0 but SUM(Field) returns NULL if there are no matching rows.

You need an ISNULL or COALESCE

COALESCE or ISNULL