mysql sum 没返回,如果没有找到任何值，我如何在MySQL中获得SUM函数以返回'0'？

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

解决方案

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

More Information:

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

MySQL 5.5.32 Schema Setup:

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:

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

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