如果不存在记录，如何选择总和-或-0? [英] How to select sum -or- 0 if no records exist?

问题描述

我需要编写一个查询，该查询返回满足特定条件的所有值的总和，但是如果未找到任何行，则查询需要返回0，而不是null.例如:

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  |
+---------------+-----+

此查询:

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

应该并且确实返回15.但是:

should, and does, return 15. However:

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

应返回0，但确实返回null.

有人可以解释这是否可能吗?

Can someone explain if this is possible?

推荐答案

怎么样:

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

COALESCE 函数基本上说返回第一个参数，除非它为null，否则将返回第二个参数"-在这种情况下非常方便.

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.

这篇关于如果不存在记录，如何选择总和-或-0?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！