How to Prevent MySQL SUM Function from Returning NULL?

How to Handle NULL Values in MySQL SUM Function

In MySQL, SUM functions often return NULL when there are no values found, which can be inconvenient. This article will explore how to use the COALESCE function to ensure that SUM always returns '0' when no values are found.

Problem:

Consider a query like:

<code class="sql">SELECT SUM(Column_1)
FROM Table
WHERE Column_2 = 'Test'</code>

If there are no entries in Column_2 that meet the criteria, the function will return NULL. However, we may prefer it to return '0' instead.

Solution: Using COALESCE

To address this issue, use the COALESCE function. COALESCE takes multiple values as arguments and returns the first non-NULL value. By using it alongside the SUM function, we can ensure that the result is always '0' when NULL is encountered.

<code class="sql">SELECT COALESCE(SUM(column),0)
FROM   table
WHERE  ...</code>

Example:

Let's use a SQL fiddle to demonstrate the COALESCE function: http://www.sqlfiddle.com/#!2/d1542/3/0

Additional Information:

The COALESCE function can also be used with other functions to handle NULL values. Here's an example using three tables with different values:

MySQL 5.5.32 Schema Setup:

<code class="sql">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);</code>

Query:

<code class="sql">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</code>

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

The above is the detailed content of How to Prevent MySQL SUM Function from Returning NULL?. For more information, please follow other related articles on the PHP Chinese website!