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

mysql sum
ewok picture ewok · Jan 17, 2012 · Viewed 51.6k times · Source

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

Mike Christensen picture Mike Christensen · Jan 17, 2012

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.

Related questions

How do I get SUM function in MySQL to return '0' if no values are found?

Say I have a simple function in MySQL: SELECT SUM(Column_1) FROM Table WHERE Column_2 = 'Test' If no entries in Column_2 contain the text 'Test' then this function returns NULL, while I would like it to return 0. I'm aware that …

Read More
MYSQL Sum Query with IF Condition

I am building a query for a report with multiple IF conditions on the SUM. I am having problems with a multiple IF conditions on the SUM. Here is the query: SELECT SUM(`totalamount`) AS Total, SUM(`PayPalFee`) AS Fees, …

Read More
MySql sum elements of a column

I have a table with 3 columns (A,B,C). I want to select some rows from the table and then the MySQL to return a single row having the values added on each column... A B C 1. 2 2 2 2. 4 4 4 3. 6 7 8 MySql should return …

Read More