Sum null as 0 mysql

Use COALESCE to avoid that outcome.

SELECT COALESCE[SUM[column],0]
FROM   table
WHERE  ...

To see it in action, please see this sql fiddle: //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 |

MySQLMySQLi Database

MySQL Database Training for Beginners

39 Lectures 5.5 hours

Simon Sez IT

More Detail

Python programming with MySQL database: from Scratch

152 Lectures 16 hours

Metla Sudha Sekhar

More Detail

Learn MySQL from scratch for Data Science and Analytics

87 Lectures 5.5 hours

Metla Sudha Sekhar

More Detail

To return Sum as ‘0’ if no values are found, use IFNULL or COALESCE commands.

The following is the syntax for IFNULL.

SELECT IFNULL[SUM[NULL], 0] AS aliasName;

Let us now implement the above syntax in the following query.

mysql> SELECT IFNULL[SUM[NULL], 0] AS SUMOFTWO;

The following is the output of the above query, which returns 0.

+----------+
| SUMOFTWO |
+----------+
|        0 |
+----------+
1 row in set [0.00 sec]

Here is the syntax for COALESCE.

mysql> SELECT COALESCE[SUM[NULL],0] as SUMOFTWO;

The following is the output that returns 0 using the SUM[] function.

+----------+
| SUMOFTWO |
+----------+
|        0 |
+----------+
1 row in set [0.00 sec]

George John

Updated on 30-Jul-2019 22:30:23

• Related Questions & Answers
• How to select sum or 0 if no records exist in MySQL?
• How can I customize the output of MySQL SUM[] function to 0 instead of NULL when there are no matching rows?
• How can I return 0 for NULL in MySQL?
• What will MySQL CHAR_LENGTH[] function return if I provide NULL to it?
• How MySQL evaluates if I will use an expression within SUM[] function?
• How MySQL SUM[] function evaluates if the column having NULL values too?
• How to return only unique values [no duplicates] in MongoDB?
• MySQL SUM function to add decimal values
• Sum if all rows are not null else return null in MySQL?
• How MySQL SUM[] function evaluates if it is used with SELECT statement that returns no matching rows?
• Can I use SUM[] with IF[] in MySQL?
• How do I detect if a table exist in MySQL?
• What would be the output of MySQL SUM[] function if a column having no values has been passed as its argument?
• How do I get the average string length in MySQL?
• How do I return multiple results in a MySQL subquery with IN[]?

Previous Page Print Page Next Page  

Advertisements

How do you sum zero a NULL in SQL?

Does sum ignore NULL values MySQL?

Can you sum a NULL value in SQL?

Does sum ignore NULL values?