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?

I think none of the answers (including the accepted answer) address the question of "what's the problem" adequately. This is non-standard behavior, by mathematical and computer science conventions. The sum of zero numbers is zero, not null. The coalesce method is a workaround for this problematic behavior by (at least) MySQL. Why does sum work that way? — Bill Clark, Feb 24 '21 at 23:47

score 119 · Accepted Answer · answered Jun 11 '13 at 15:46

119

Try this:

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

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

answered Jun 11 '13 at 15:46

alwaysVBNET

3,150
8
32
65

6

COALESCE is a lot better here then isnull – Scott Selby Jun 11 '13 at 15:47
@ScottSelby: How so? AFAIK, `isnull` is more predictable in that it always returns the data type of the first argument. – Andomar Jun 11 '13 at 15:50
@Andomar "An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL" - I just always use COALESCE , it will always catch for null even if balance is not nullable – Scott Selby Jun 11 '13 at 15:54
Prior to SQL 2008 ISNULL was considerably faster than COALESCE. This makes sense as the code behind ISNULL has to deal with a fixed number of input variables (2) and COALESCE has to be designed to work with any number of variables, so this will invariably involve extra computations. In SQL 2008, I've seen a thread where people say that the performance is now for all intent and purpose the same for both (I wonder if the optimiser just converts it to ISNULL if there are only 2 variables). [from here](http://www.sqlservercentral.com/Forums/Topic832742-392-1.aspx) – alwaysVBNET Jun 11 '13 at 15:56
@ScottSelby +1 You are right, it is recommended in publications by Microsoft MVPs – Ivan Golović Jun 11 '13 at 16:38
@Andomar 'COALESCE' is the preffered way, not the 'ISNULL'. Reference to what I'm talking about can be found in "Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012" on pages 50 and 51. – Ivan Golović Jun 11 '13 at 16:39
3

Reasons why COALESCE should be used are: 1) ISNULL is non-standard while COALESCE is. 2) COALESCE supports multiple inputs whereas ISNULL supports only 2 inputs. 3) When using SELECT INTO construct, if you have a column defined as `COALESCE(col1, 0) AS newcol1` - if the source attribute `col1` is defined as allowing NULLs `newcol1` will also allow NULLs, if you have `ISNULL(col1, 0) AS newcol1` it will create an attribute that will not allow NULLs 4) With `COALESCE` type of the output will be the same as the type of the returned element, `ISNULL` will return the type of the first argument – Ivan Golović Jun 11 '13 at 16:48
3

As for reason number 4. from my previous comment, consider following sample to see why COALESCE might be more appropriate: `DECLARE @x AS VARCHAR(3) = NULL,@y AS VARCHAR(10) = '1234567890'; SELECT COALESCE(@x, @y) AS [COALESCE], ISNULL(@x, @y) AS [ISNULL]` This sample some previous samples were taken from book `Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012` by Itzik Ben-Gan, Dejan Sarka, Ron Talmage. – Ivan Golović Jun 11 '13 at 17:00
Its a very good example @IvanG. How about performance considerations when you know the inputs, for example isnull(balance, 0) vs coalsece(balance, 0) – kuklei May 19 '20 at 10:49
Thanks @kuklei, you can find discussion about performance differences on: https://dba.stackexchange.com/questions/4274/performance-difference-for-coalesce-versus-isnull – Ivan Golović May 21 '20 at 16:30

score 18 · Answer 2 · answered Jun 11 '13 at 15:45

18

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'

answered Jun 11 '13 at 15:45

Andomar

232,371
49
380
404

2

it will also return `null` if one of the rows have a `null` balance – pcv May 19 '16 at 20:47
This is exactly what I needed to know, i.e. if `null` is returned by `sum` in cases when the number of records is 0. +1 for giving the reason of the behaviour. – Sнаđошƒаӽ Nov 17 '16 at 12:25
1

@pcv at least in SQL Server 2016 that is not true. Sum() treats null as zero if there are other records with non-null values. Only if all balance records are null `sum` returns null. So Andomar's answer returns 0 in both cases - i.e. if no records exist or if all existing records are null. – nawfal Mar 16 '18 at 04:09

score 5 · Answer 3 · answered Jun 11 '13 at 15:45

5

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

answered Jun 11 '13 at 15:45

David Jashi

4,490
1
21
26

That would be a fix if the OP used Oracle, and if there was a record with a `null` balance, as opposed to no records – Andomar Jun 11 '13 at 15:46
Sorry, haven't noticed that he uses SQL Server. I'll edit it to be compatible with most implementations. – David Jashi Jun 11 '13 at 15:49
I needed to put COALESCE wrapper around the SUM if no records and on MySQL. – Shaun O'Hagan Dec 28 '16 at 14:38

bendataclear · Answer 4 · 2013-06-11T16:03:39.067

1

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

edited Jun 11 '13 at 16:03

answered Jun 11 '13 at 15:48

bendataclear

3,802
3
32
51

COUNT returns a different value 0 - meaning there are zero rows matching query , if there are 2 rows count will will return 2 , not the sum of the 2 rows – Scott Selby Jun 11 '13 at 15:51
@ScottSelby, sorry maybe I wasn't clear, I meant he may have been thinking of `COUNT` as returning zero if there are no matching rows, not that `COUNT` is a replacement for `SUM`. – bendataclear Jun 11 '13 at 16:00

Guilherme Fernandes · Answer 5 · 2022-06-17T18:23:14.523

0

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

edited Jun 17 '22 at 18:23

answered Jun 14 '22 at 14:56

Guilherme Fernandes

1
1

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/32018802) – Charlieface Jun 19 '22 at 04:28

score -1 · Answer 6 · answered Jun 11 '13 at 15:45

-1

Try this:

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

answered Jun 11 '13 at 15:45

Yuriy Galanter

38,833
15
69
136

That would be a fix if there was a record with a null balance, as opposed to no records – Andomar Jun 11 '13 at 15:47
`if the customer has no balance` wasn't clear whether there's no record OR there is a record but the balance is NULL – Yuriy Galanter Jun 11 '13 at 15:48

My Select SUM query returns null. It should return 0

6 Answers6

Linked

Related