CODEWITHSUNDEEP
mysqlfunctionnullsum
Nick
Nick

Reputation: 4462

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.

Upvotes: 188

Views: 199555

Answers (4)

Sreejith N
Sreejith N

Reputation: 41

if sum of column is 0 then display empty

select if(sum(column)>0,sum(column),'')
from table

Upvotes: 0

Brad Christie
Brad Christie

Reputation: 101594

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 |

Upvotes: 388

Sashi Kant
Sashi Kant

Reputation: 13455

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

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

Upvotes: 6

Mark Byers
Mark Byers

Reputation: 837916

Use IFNULL or COALESCE:

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.

Upvotes: 84

Related Questions