CODEWITHSUNDEEP
sqlsql-server
nec tso
nec tso

Reputation: 669

My Select SUM query returns null. It should return 0

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?

Upvotes: 62

Views: 89795

Answers (6)

Guilherme Fernandes
Guilherme Fernandes

Reputation: 1

Note that it happens only if all values are NULL, or has no values/entries at all.

You can (and should) always test what happens behind the curtains.

Example with custom values (PostgreSQL SQL):

WITH test_data (a, b) as (
  SELECT * 
  FROM (VALUES 
         ('example1', 1), 
         ('example2', 2), 
         ('example3', NULL), 
         ('example3', 3), 
         (NULL, NULL), 
         (NULL, 5), 
         (NULL, 5),
         ('example4', NULL)
       ) t
)
SELECT 
  a,
  SUM(b) AS b
FROM test_data
GROUP BY 1

Upvotes: 0

bendataclear
bendataclear

Reputation: 3850

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

Upvotes: 1

alwaysVBNET
alwaysVBNET

Reputation: 3310

Try this:

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

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

Upvotes: 125

suff trek
suff trek

Reputation: 39777

Try this:

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

Upvotes: -1

David Jashi
David Jashi

Reputation: 4511

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

Upvotes: 5

Andomar
Andomar

Reputation: 238088

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'

Upvotes: 18

Related Questions