Home > Database > Mysql Tutorial > body text

How to Return \'0\' Instead of NULL When Using MySQL\'s SUM Function on Empty Datasets?

Barbara Streisand
Release: 2024-11-03 03:34:30
Original
498 people have browsed it

How to Return '0' Instead of NULL When Using MySQL's SUM Function on Empty Datasets?

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'
Copy after login

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
Copy after login

You will obtain the following 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

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

The above is the detailed content of How to Return \'0\' Instead of NULL When Using MySQL\'s SUM Function on Empty Datasets?. For more information, please follow other related articles on the PHP Chinese website!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template