Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

People also ask

How do you find the sum of zero if no records exist?

You can use aggregate function sum() inside COALESCE(). The below syntax returns the sum of all if the record exists otherwise 0 is returned.

How do I make NULL values 0 in MySQL?

Use IFNULL or COALESCE() function in order to convert MySQL NULL to 0. Insert some records in the table using insert command. Display all records from the table using select statement.

How do I use Isnull with sum in SQL?

SELECT SUM(ISNULL(Salary, 10000) AS Salary FROM Employee; Output: In MySQL, ISNULL() function is used to test whether an expression is NULL or not. If the expression is NULL it returns TRUE, else FALSE.

Does sum work with NULL?

Yes its safe . You can use Sum without handling NULL Value.


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 |

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.


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