My Select SUM query returns null. It should return 0

SqlSql Server

Sql Problem Overview

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?

Sql Solutions

Solution 1 - Sql

Try this:

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

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

Solution 2 - Sql

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'

Solution 3 - Sql

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

Solution 4 - Sql

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

Solution 5 - Sql

Try this:

select sum(IsNull(balance,0)) from mytable where customer = 'john'
Categories
Recommended Sql Solutions
Recommended Sql Server Solutions

Previous Solution:

Can't ssh to AWS EC2: Identity file not accessible

SshAmazon Ec2Ubuntu 12.04

Next Solution:

How to print current date on python3?

PythonDateDatetimeTime

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questionnec tsoView Question on Stackoverflow
Solution 1 - SqlalwaysVBNETView Answer on Stackoverflow
Solution 2 - SqlAndomarView Answer on Stackoverflow
Solution 3 - SqlDavid JashiView Answer on Stackoverflow
Solution 4 - SqlbendataclearView Answer on Stackoverflow
Solution 5 - SqlYuriy GalanterView Answer on Stackoverflow