Как выбрать sum-or-0, если записей нет?

Мне нужно написать запрос, который возвращает сумму всех значений, удовлетворяющих определенным критериям, но запрос должен возвращать 0, если строки не найдены, а не null. Например:

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. Однако:

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

должен возвращать 0, но возвращает null.

Может кто-нибудь объяснить, возможно ли это?

Ответы

Ответ 1

Как насчет:

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

Функция COALESCE в основном говорит: "Верните первый параметр, если он не равен нулю, и в этом случае вернет второй параметр". Это очень удобно в этих сценариях.

Ответ 2

Проверьте документацию MySQL для IFNULL.

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

Конечно, это предполагает, что ваше поле num имеет значение NULL и не имеет значения по умолчанию. Другим возможным решением было бы установить значение по умолчанию 0 для поля num, которое должно решить проблему, с которой вы столкнулись.

Ответ 3

Это работает:

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

IF() принимает три параметра: (1) оператор, (2) значение, которое следует применять, если оператор является истинным, и (3) значение, которое следует применять, если утверждение ложно.

Ответ 4

Чтобы сделать это правильно, вы можете провести различие между случаем, когда есть фактические результаты NULL в данных, которые вы суммируете, и случай, когда суммы вообще не суммируются.

Предположим, что мы имеем:

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

Мы хотим, чтобы пустые суммы были равны нулю, а суммы с NULL были NULL. Один (довольно мучительный) способ сделать это:

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)

Возможно, там лучший способ, о котором я не думал.

К сожалению, стандарт SQL определяет SUM как null, когда не суммируются никакие элементы, и у MySQL нет выбора, кроме как следовать этому стандарту.