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?

Answer 1

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.

Answer 2

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.

Answer 3

The short way is:

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

Answer 4

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.

Answer 5

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.