如果没有找到任何值,我如何在MySQL中获得SUM函数以返回'0'? [英] How do I get SUM function in MySQL to return '0' if no values are found?
问题描述
SELECT SUM(Column 1)from Table WHERE Column 2 ='Test '
如果第2列中没有包含文本'Test'的条目,则此函数返回NULL,而I希望它返回0.
我知道类似的问题已经在这里被问了几次,但我还没有能够调整答案我的目的,所以我会很感激一些帮助,以获得这种排序。 使用 COALESCE
以避免
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $' 。
若要查看它的动作,请参阅以下sql提琴: http://www.sqlfiddle.com/#!2/d1542/3/0
$ b给出三张表(一个包含所有数字,一个包含所有数字,另一个包含所有数字,另一个包含所有数字)与混合物):
SQL小提琴 MySQL 5.5.32架构设置: 查询1 : 结果 : Say I have a simple function in MySQL: 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 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: Query 1:
这篇关于如果没有找到任何值,我如何在MySQL中获得SUM函数以返回'0'?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
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) ,(空),(5),(空),(6),(空);
CREATE TABLE bar
(
id INT不为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);
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 |描述| EXPECTED_SUM | ACTUAL_SUM |
| ------------ | --------------------- | ---------- ---- | ------------ |
| foo |混合null / non-null | 21 | 21 |
| bar |所有非null | 21 | 21 |
| baz |全为null | 0 | 0 |
SELECT SUM(Column 1) from Table WHERE Column 2='Test'
COALESCE
to avoid that outcome.SELECT COALESCE(SUM(column),0)
FROM table
WHERE ...
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);
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 |