如果没有找到值，如何在MySQL中得到SUM函数返回“0”?

Say I have a simple function in MySQL:

假设我在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.

如果列2中没有条目包含文本“Test”，那么这个函数将返回NULL，而我希望它返回0。

I'm aware that a similar question has been asked a few times here, but I haven't been able to adapt the answers to my purposes, so I'd be grateful for some help to get this sorted.

我知道在这里也有人问过类似的问题，但是我还没能把答案调整到我的目的，所以我很感激能帮我解决这个问题。

3 个解决方案

#1

226

Use COALESCE to avoid that outcome.

使用合并来避免这种结果。

SELECT COALESCE(SUM(column),0)
FROM   table
WHERE  ...

To see it in action, please see this sql fiddle: http://www.sqlfiddle.com/#!2/d1542/3/0

要查看它的实际操作，请查看此sqlfiddle: http://www.sqlfiddle.com/#!

More Information:

更多信息:

Given three tables (one with all numbers, one with all nulls, and one with a mixture):

给定三个表(一个是所有的数字，一个是空的，一个是混合的):

SQL Fiddle

SQL小提琴

MySQL 5.5.32 Schema Setup:

MySQL 5.5.32模式设置:

CREATE TABLE foo
(
  id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  val   INT
);

INSERT INTO foo (val) VALUES
(null),(1),(null),(2),(null),(3),(null),(4),(null),(5),(null),(6),(null);

CREATE TABLE bar
(
  id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  val   INT
);

INSERT INTO bar (val) VALUES
(1),(2),(3),(4),(5),(6);

CREATE TABLE baz
(
  id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  val   INT
);

INSERT INTO baz (val) VALUES
(null),(null),(null),(null),(null),(null);

Query 1:

查询1:

SELECT  'foo'                   as table_name,
        'mixed null/non-null'   as description,
        21                      as expected_sum,
        COALESCE(SUM(val), 0)   as actual_sum
FROM    foo
UNION ALL

SELECT  'bar'                   as table_name,
        'all non-null'          as description,
        21                      as expected_sum,
        COALESCE(SUM(val), 0)   as actual_sum
FROM    bar
UNION ALL

SELECT  'baz'                   as table_name,
        'all null'              as description,
        0                       as expected_sum,
        COALESCE(SUM(val), 0)   as actual_sum
FROM    baz

Results:

结果:

| TABLE_NAME |         DESCRIPTION | EXPECTED_SUM | ACTUAL_SUM |
|------------|---------------------|--------------|------------|
|        foo | mixed null/non-null |           21 |         21 |
|        bar |        all non-null |           21 |         21 |
|        baz |            all null |            0 |          0 |

#2

Use IFNULL or COALESCE:

使用IFNULL或合并:

SELECT IFNULL(SUM(Column1), 0) AS total FROM...

SELECT COALESCE(SUM(Column1), 0) AS total FROM...

The difference between them is that IFNULL is a MySQL extension that takes two arguments, and COALESCE is a standard SQL function that can take one or more arguments. When you only have two arguments using IFNULL is slightly faster, though here the difference is insignificant since it is only called once.

它们之间的区别是，如果null是一个MySQL扩展，它接受两个参数，而合并是一个标准的SQL函数，可以接受一个或多个参数。当您只有两个参数使用IFNULL时稍微快一点，尽管这里的差异是微不足道的，因为它只被调用一次。

#3

Can't get exactly what you are asking but if you are using an aggregate SUM function which implies that you are grouping the table.

不能得到你想要的，但是如果你使用的是一个总和函数这意味着你在对表进行分组。

The query goes for MYSQL like this

查询是这样处理MYSQL的。

Select IFNULL(SUM(COLUMN1),0) as total from mytable group by condition

#1

226