How to Retrieve '0' from MySQL's SUM Function When No Values Exist

The SUM function in MySQL offers a convenient way to aggregate numerical values. However, when no matching rows are found during the query, the SUM function typically returns a NULL value. For some use cases, it may be more desirable to return '0' instead of NULL.

Utilizing COALESCE to Resolve the Issue

The solution to this problem lies in the COALESCE function. COALESCE allows you to specify a default value to be returned when the first argument evaluates to NULL.

Consider the following example:

SELECT COALESCE(SUM(Column_1),0)
FROM Table
WHERE Column_2 = 'Test'

In this query, COALESCE wraps around the SUM function. If the SUM of Column_1 for rows where Column_2 equals 'Test' returns a NULL value, COALESCE will instead provide '0'.

Demonstration and More Information

Visit http://www.sqlfiddle.com/#!2/d1542/3/0 for a live demonstration of the solution.

To delve deeper into the nuances of COALESCE, consider the following scenario involving three tables with different value distributions:

• Table 'foo' contains a mix of NULL and non-NULL values.
• Table 'bar' solely contains non-NULL values.
• Table 'baz' exclusively contains NULL values.

By applying the following query:

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

You will obtain the following results:

As you can observe, the COALESCE function efficiently returns '0' for the 'baz' table, where all values are NULL.