Nick Nick - 2 months ago 6
MySQL Question

How do I get SUM function in MySQL to return '0' if no values are found?

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.

Answer

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

SQL Fiddle

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

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 |