00 Votes

MySQL: Select sum or 0 if no records found

Question by Guest | 2015-11-04 at 23:40

I would like to read out the sum of the values from one of my columns out of my MySQL database. I am using the following query for this.

SELECT SUM(col) FROM tab WHERE ...

There is no problem with this approach whenever the query finds at least one record. In this case, the correct sum is the result.

Unfortunately, whenever no record is matching, the query is not working anymore. In this case, the result is always NULL. However, instead of NULL, I would like to have 0 as returned value. Is there any possibility to do that?

ReplyPositiveNegative
1Best Answer1 Vote

You can, for example, use the function COALESCE or IFNULL to change your query. I will give you an example:

SELECT COALESCE(SUM(col), 0) FROM tab WHERE ...
SELECT IFNULL(SUM(col), 0) FROM tab WHERE ...

COALESCE is returning the first passed parameter that is not NULL. That means: If SUM(col) is NULL, 0 as the second parameter will be returned. IFNULL is working in a similar way. If the first parameter passed to IFNULL is NULL, the second parameter will be returned, otherwise the first.
2015-11-05 at 00:29

ReplyPositive Negative
Reply

Related Topics

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own article on askingbox.com. That’s how it’s done.