NULL Values in Aggregate Functions: Handling Them Correctly

1. Why NULL values in aggregate functions deserve their own topic

NULL values in aggregate functions follow their own, sometimes counterintuitive rules that differ fundamentally from how NULL behaves in ordinary comparisons. While a comparison like column = NULL in SQL always evaluates to unknown instead of true or false, aggregate functions do not treat NULL as a third truth value at all, but simply as the absence of a value, which they ignore during calculation. This distinction is the starting point for most misunderstandings around NULL values in aggregate functions.

In practice this behavior produces results that seem surprising at first glance, even though they exactly match the SQL specification. A column with missing ratings, incompletely maintained prices or optional measurements can noticeably skew an average calculation as soon as NULL is not deliberately treated as its own case. Anyone who has internalized the rules for NULL values in aggregate functions avoids one of the most common and, at the same time, hardest to find classes of bugs in reporting.

This article systematically goes through the behavior of COUNT, SUM, AVG, MIN, MAX and boolean aggregate functions with NULL values, shows typical pitfalls with division and GROUP BY, and introduces COALESCE and NULLIF as central tools for controlling NULL behavior in aggregate functions deliberately instead of leaving it to chance.

2. COUNT(*) vs. COUNT(column): the difference with NULL

Probably the most common stumbling block with NULL values in aggregate functions is the difference between COUNT(*) and COUNT(column). COUNT(*) counts every row of the group, regardless of whether individual columns contain NULL or not. COUNT(column), by contrast, counts only rows in which exactly that column contains a value other than NULL, and completely ignores NULL rows during the count.

This distinction matters especially when a table contains optional columns, such as a rating column that is only filled when a rating was actually submitted. COUNT(*) then returns the total number of orders, while COUNT(rating) returns only the number of orders that were actually rated. Anyone who needs both numbers, for example to compute a rating rate, must deliberately use both variants side by side instead of accidentally using only one of them.

-- COUNT(*) vs. COUNT(column): NULL values in aggregate functions
SELECT
    product_id,
    COUNT(*) AS total_orders,          -- counts every row, NULL or not
    COUNT(rating) AS rated_orders,     -- ignores rows where rating IS NULL
    ROUND(100.0 * COUNT(rating) / COUNT(*), 1) AS rating_rate_pct
FROM order_reviews
GROUP BY product_id;

-- Result (excerpt)
-- product_id | total_orders | rated_orders | rating_rate_pct
--          1 |          200 |          140 |            70.0

3. SUM and AVG ignore NULL: consequences for averages

SUM and AVG treat NULL values in aggregate functions identically to each other: both ignore NULL completely instead of interpreting it as zero or as an erroneous value. SUM only adds the values that are actually present and returns NULL itself, not zero as a number, for a group made up exclusively of NULL values. AVG divides the sum of the present values by the number of present values, not by the total number of rows in the group, which is the actual reason behind many surprising average values.

This exact property of AVG is frequently overlooked: a group of five rows, of which only three contain a value other than NULL, returns with AVG the average of these three values, not the average across all five rows with the missing values counted as zero. Anyone who instead expects an average across all rows, including the missing ones counted as zero, must explicitly turn NULL into zero with COALESCE before AVG is applied, otherwise the result will noticeably diverge from the business expectation.

-- AVG ignores NULL rows entirely, which changes the denominator
SELECT
    product_id,
    AVG(discount_pct) AS avg_discount_ignoring_null,        -- divides by non-NULL rows only
    AVG(COALESCE(discount_pct, 0)) AS avg_discount_with_zero -- divides by ALL rows
FROM order_items
GROUP BY product_id;

-- The two results can differ substantially when many rows have no discount at all

4. Using COALESCE and NULLIF deliberately against unwanted NULL

COALESCE is the central tool for deliberately controlling unwanted NULL behavior in aggregate functions. The function returns the first value of a list that is not NULL, and is typically used to specify an explicit replacement value such as zero before a column enters an aggregate function. This allows switching the behavior of AVG deliberately from the default semantics, which ignores NULL, to a semantics that includes missing values as zero in the calculation.

NULLIF is the logical counterpart to COALESCE: instead of replacing NULL with a value, NULLIF deliberately turns a specific value into NULL when two expressions are equal. This is particularly useful when preparing data for aggregation, for example when a sentinel value such as minus one in the raw data actually represents a missing measurement and should be treated as NULL before aggregation, so that AVG and SUM correctly ignore this sentinel instead of incorrectly counting it as a real measurement.

-- COALESCE: turn NULL into an explicit value before aggregation
SELECT customer_id, SUM(COALESCE(bonus_points, 0)) AS total_bonus_points
FROM loyalty_transactions
GROUP BY customer_id;

-- NULLIF: turn a sentinel value into NULL so aggregate functions ignore it
SELECT sensor_id, AVG(NULLIF(reading, -1)) AS avg_reading_excluding_sentinel
FROM sensor_readings
GROUP BY sensor_id;

5. Division by zero and NULLIF as a safeguard against errors

A particularly practical use case for NULLIF in the context of NULL values in aggregate functions is protecting against a division by zero, which triggers an error in almost every database system and aborts the entire query. Reports that compute rates or percentages, such as conversion rates from COUNT results, are especially vulnerable as soon as the denominator happens to be zero for a particular group.

NULLIF(denominator, 0) turns a denominator of zero into NULL, and a division by NULL reliably returns NULL in SQL instead of an error. The result for the affected row then becomes NULL instead of a runtime error, which most reporting tools can render as an empty or unavailable value, instead of crashing the entire report. This technique belongs in every query that performs a division based on aggregated counts.

-- Protect against division by zero using NULLIF
SELECT
    campaign_id,
    COUNT(*) FILTER (WHERE converted) AS conversions,
    COUNT(*) AS total_visits,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE converted) / NULLIF(COUNT(*), 0),
        2
    ) AS conversion_rate_pct
FROM campaign_visits
GROUP BY campaign_id;

6. GROUP BY and NULL: its own group instead of exclusion

Another important aspect of NULL values in aggregate functions concerns the interplay with GROUP BY. Contrary to what one might expect, GROUP BY does not exclude rows with NULL in the grouping column, but instead forms a single, shared group for all NULL rows. Every row whose grouping column is NULL therefore ends up together in exactly one result row, regardless of how many different business reasons exist for the respective NULL.

This can lead to an unexpected merge when NULL in the grouping column arises from different causes at once, for example "category not yet assigned" and "category deliberately left empty". Aggregate functions such as SUM or COUNT within this NULL group then merge business wise different cases into a single row. Anyone who wants to avoid this mixing should turn NULL in the grouping column into a meaningful placeholder text such as "Not assigned" with COALESCE before aggregation, so the group remains clearly identifiable in the result.

7. Boolean aggregation with NULL: BOOL_OR and COUNT(CASE WHEN...)

PostgreSQL offers BOOL_OR and BOOL_AND as dedicated aggregate functions for boolean columns, which also ignore NULL values in aggregate functions, as is common for aggregate functions in general. BOOL_OR returns true as soon as at least one non-NULL row of the group is true, BOOL_AND returns true only when all non-NULL rows are true. In database systems without native boolean aggregate functions, such as MySQL or SQL Server, COUNT(CASE WHEN condition THEN 1 END) takes over the same task, supplemented with a comparison against zero or the total count.

The advantage of COUNT(CASE WHEN...) over a direct SUM(condition) construction is that CASE WHEN explicitly controls what should happen with NULL in the checked column, instead of relying on implicit behavior of the comparison operators. This construction is also the foundation of conditional aggregation, with which several metrics for different conditions can be computed side by side in a single query without needing several separate queries.

-- Boolean aggregation with NULL-aware conditional counting
SELECT
    order_id,
    BOOL_OR(is_returned) AS any_item_returned,         -- PostgreSQL native
    COUNT(CASE WHEN is_returned THEN 1 END) AS returned_item_count,
    COUNT(*) AS total_item_count
FROM order_items
GROUP BY order_id;

8. HAVING and NULL comparisons: common pitfalls

HAVING filters results after aggregation, and here too NULL values in aggregate functions play a role that is easily overlooked. An expression like HAVING SUM(amount) = NULL never returns a result, because a comparison with NULL in SQL always evaluates to unknown instead of true, even if SUM actually did return NULL. The correct expression is HAVING SUM(amount) IS NULL, using the explicit IS NULL operator instead of an equality comparison.

This pitfall particularly affects reports that want to filter out groups with no valid values at all, such as products without a single rating. Anyone who accidentally writes = NULL instead of IS NULL gets no error message, just a plainly empty or incomplete result, without the reason being immediately obvious. This distinction between an equality comparison and an IS NULL check is one of the most fundamental, yet most frequently overlooked rules when dealing with NULL in SQL at all.

9. NULL behavior of aggregate functions compared

The following overview summarizes how the most important aggregate functions handle NULL values, so the correct behavior is expected from the start when writing a report.

Anyone who deliberately handles NULL values in aggregate functions from the start, instead of relying on implicit behavior, avoids not only wrong average values but also hard to trace discussions about why a report, on closer inspection, still is not quite right.

10. Summary

NULL values in aggregate functions follow a consistent, but easily overlooked rule: aggregate functions ignore NULL during calculation instead of treating it as zero or an error value. COUNT(*) and COUNT(column) therefore return different results, AVG divides only by the count of present values, and SUM over a group made up exclusively of NULL values returns NULL itself. COALESCE deliberately replaces NULL with an explicit value, NULLIF conversely deliberately turns a specific value into NULL.

Anyone who knows these rules avoids the most common sources of error in reporting: skewed averages, divisions by zero and silently overlooked HAVING conditions with a wrong equality comparison instead of IS NULL. GROUP BY also deserves special attention, because NULL rows are merged there into their own, potentially business wise mixed group, instead of simply being excluded.

11. FAQ: NULL Values in Aggregate Functions