My Select SUM query returns null. It should return 0

sql

null-values

aggregate-functions

coalesce

byNikita Barsukov·Jan 13, 2025

To ensure a 0 result instead of NULL from a SUM query, use the COALESCE function. It simplifies your interaction with potential NULL SUM results by converting them into 0.

Here's a simple SQL snippet:

SELECT COALESCE(SUM(your_column), 0) AS total FROM your_table;

Why is my SUM returning null?

The SUM function in SQL, designed to aggregate data through addition, will give a NULL result if the targeted set is void of data. This could be due to all rows being filtered by the WHERE clause or the queried table being empty.

Handling null in SQL: SUM versus COUNT

Different aggregate functions in SQL have unique behaviors when handling NULL values. For instance, unlike SUM, COUNT will still compute the number of rows even when NULL values are present. But when you specify a column like 'COUNT(column_name)', NULL values are skipped.

Null to zero: COALESCE and ISNULL

When you want to convert NULL results to 0, you could employ the use of COALESCE or ISNULL:

SELECT ISNULL(SUM(your_column), 0) AS total FROM your_table;

These functions work equally well by replacing NULL with a designated value, in our case 0.

Testing query behavior with CTE

You can use the built-in WITH clause in SQL to set up test data for observing your query behavior using a common table expression (CTE) with predefined values. Here's how you can set it up:

WITH DummyData(your_column) AS (
    SELECT NULL UNION ALL  -- Pretending to be an opera singer
    SELECT NULL UNION ALL  -- Lost in thought about the meaning of NULL 
    SELECT NULL            -- Null, the final frontier
)
SELECT COALESCE(SUM(your_column), 0) AS total FROM DummyData;

This will return 0, demonstrating how COALESCE comes to the rescue when aggregate functions face NULL values.

Bullet-proofing your SQL SUM: Pro tips

Here are some additional tips to guard your SUM against unwanted NULL values:

Check for unintentional exclusions: Ensure you haven't written a Frankenstein WHERE clause or overly-complicated joins that accidentally omit too many rows.
Understand other aggregates' behavior: Know how other aggregate functions like AVG and MAX work with NULL. NULL in SQL wasn't built in a day.
Watch out for actual NULL values in the data: Sometimes the problem might stem from NULL values in your data, rather than an empty set. Take the NULL bull by the horns: COALESCE(your_column, 0) before the SUM.
Mind the data types: Ensure that NULL values are not interfering with the data conversion or calculation. SQL is also known by its alter ego – Strictly Type Checked!