How to select sum -or- 0 if no records exist?

Question

I need to write a query that returns the sum of all values that meet a certain criteria, but the query needs to return 0 if no rows are found, rather than null. For example:

tab    
+---------------+-----+
| descr         | num |
+---------------+-----+
| hello there   | 5   |
| hi there      | 10  |
| hello         | 10  |
| hi there!     | 15  |
+---------------+-----+

This query:

SELECT sum(num) AS val FROM tab WHERE descr LIKE "%hello%";

should, and does, return 15. However:

SELECT sum(num) AS val FROM tab WHERE descr LIKE "%greetings%";

should return 0, but does return null.

Can someone explain if this is possible?

score 140 · Accepted Answer · answered Jan 17 '12 at 00:53

140

How about:

SELECT COALESCE(sum(num), 0) AS val FROM tab WHERE descr LIKE "%greetings%";

The COALESCE function basically says "return the first parameter, unless it's null in which case return the second parameter" - It's quite handy in these scenarios.

answered Jan 17 '12 at 00:53

Mike Christensen

88,082
50
208
326

3

This doesn't actually have the desired effect - this will also mask a `NULL` that's part of the `num` values themselves. – Ken Williams Nov 11 '17 at 04:46
12

This doesn't work. If there are no rows to aggregate `coalesce` won't even be called. I don't understand how this has 122 upvotes. – Mike Nov 10 '21 at 20:10
works for me in postgres at least – Riedler Apr 02 '23 at 12:57

sesser · Answer 2 · 2012-01-17T01:02:20.590

15

Check the MySQL documentation for IFNULL.

SELECT SUM(IFNULL(num, 0)) as val FROM tab WHERE descr LIKE "%greetings%";

Of course, this assumes your num field is nullable and doesn't have a default value. Another possible solution would be to set a default of 0 for the num field which should solve the issue you're having.

edited Jan 17 '12 at 01:02

answered Jan 17 '12 at 00:58

sesser

1,155
5
12

14

IFNULL should be outside SUM, otherwise if no rows are found it will return null. – Lluis Martinez Nov 30 '12 at 15:31
1

SUM already ignores NULL values. In all cases SUM(IFNULL(abc, 0)) == SUM(abc) – Eloims May 20 '19 at 20:02
1

@Eloims Your comment seems to be false. I also assumed that but it was wrong, which is why I am here looking for an answer. – Chad Befus Dec 05 '20 at 01:04
@ChadBefus I was indeed wrong. SUM will ignore NULLs, but if the column contains ONLY nulls, or the table is empty it returns NULL. Here is a fiddle to test this: https://www.db-fiddle.com/f/5hvBkzyMWKYx7mT6hKqKFS/0 – Eloims Dec 05 '20 at 16:45
@sesser this is not working with postgres – Sagar Davara May 10 '22 at 12:48
@SagarDavara OP specifically asked about mysql. I haven't used postgres enough to provide a solution for you but I'm sure the manual could shed some light – sesser May 11 '22 at 13:04
@sesser even if in mysql, if the join table does not have any rows that satisfy the where condition, then this will not work – Sagar Davara May 12 '22 at 03:34
@SagarDavara now we’re talking about joins? Use inner join so you don’t get null values. – sesser May 13 '22 at 05:10

Denis Ostrovsky · Answer 3 · 2022-02-10T13:03:23.670

5

The short way is:

SELECT IFNULL(SUM(num), 0) as val FROM tab WHERE descr LIKE "%greetings%";

edited Feb 10 '22 at 13:03

answered Nov 02 '21 at 19:03

Denis Ostrovsky

519
1
6
16

score 3 · Answer 4 · answered Nov 11 '17 at 05:39

To do this properly, you may want to distinguish between the case where there are actual NULL results in the data you're summing, and the case where there are no values at all to sum.

Suppose we have the following:

mysql> select * from t;
+----------+------+
| descr    | num  |
+----------+------+
| hiya     |    5 |
| hi there |   10 |
| yo       | NULL |
+----------+------+

We would like empty sums to be zero, but sums involving NULL to be NULL. One (rather torturous) way to do that is:

mysql> SELECT IF(has_null, NULL, total) AS sum FROM (
    ->    SELECT COALESCE(MAX(num IS NULL), 0) AS has_null, COALESCE(SUM(num), 0) AS total
    ->    FROM t WHERE num < 'ciao')
    -> AS u;
+------+
| sum  |
+------+
|    0 |
+------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT IF(has_null, NULL, total) AS sum FROM (
    ->    SELECT COALESCE(MAX(num IS NULL), 0) AS has_null, COALESCE(SUM(num), 0) AS total
    ->    FROM t)
    -> AS u;
+------+
| sum  |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> SELECT IF(has_null, NULL, total) AS sum FROM (
    ->    SELECT COALESCE(MAX(num IS NULL), 0) AS has_null, COALESCE(SUM(num), 0) AS total
    ->    FROM t WHERE descr < 'namaste')
    -> AS u;
+------+
| sum  |
+------+
|   15 |
+------+
1 row in set (0.00 sec)

mysql> SELECT IF(has_null, NULL, total) AS sum FROM (
    ->    SELECT COALESCE(MAX(num IS NULL), 0) AS has_null, COALESCE(SUM(num), 0) AS total
    ->    FROM t WHERE descr > 'namaste')
    -> AS u;
+------+
| sum  |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

Perhaps there's a better way I haven't thought of.

Unfortunately, the SQL standard defines SUM to be null when no elements are summed, and MySQL has no choice but to follow that standard.

score 2 · Answer 5 · answered Jan 17 '12 at 00:55

2

This works:

SELECT IF(SUM(num) IS NULL, 0, SUM(num)) AS val FROM tab WHERE descr LIKE "%whatever%";

IF() takes three parameters: (1) A statement, (2) the value to apply if the statement is true, and (3) the value to apply if the statement is false.

answered Jan 17 '12 at 00:55

Thomas Kelley

10,187
1
36
43

How to select sum -or- 0 if no records exist?

5 Answers5

Linked