Conditional Aggregation: Building KPI Reports with CASE WHEN

1. What conditional aggregation solves and why it matters

Conditional aggregation is the technique of embedding a condition directly inside an aggregate function, instead of filtering a table multiple times and merging the results afterward in application code. The classic use case is a dashboard that needs to show, for the same order list, the number of open, shipped, and cancelled orders at once. Without conditional aggregation, you would write three separate queries, each with its own WHERE condition, and merge the results manually afterward.

With conditional aggregation, a single query returns all three metrics as separate columns in the same result row instead. The database server reads the table only once instead of scanning it three times, which makes a noticeable performance difference on large fact tables. Conditional aggregation is therefore not just a readability concern, it is a direct optimization technique for reporting queries with multiple metrics.

This article covers both common variants of conditional aggregation, the classic CASE WHEN pattern and the more modern FILTER clause, explains typical NULL pitfalls, and shows how to compute percentages and rates directly from conditionally counted values, without extra queries or application logic.

2. The base pattern: CASE WHEN inside aggregate functions

The base pattern of conditional aggregation with CASE WHEN works by letting a CASE expression inside the aggregate function decide which value per row goes into the calculation. For a count, the pattern is COUNT(CASE WHEN condition THEN 1 END), where END without an ELSE automatically returns NULL for every row that does not meet the condition, and COUNT famously ignores NULL values. For a sum, the equivalent pattern is SUM(CASE WHEN condition THEN amount ELSE 0 END), where an explicit ELSE 0 is required so SUM returns a number instead of NULL once no row in the group satisfies the condition.

The difference between COUNT(CASE WHEN...) and SUM(CASE WHEN...) in conditional aggregation therefore lies in the ELSE branch: for COUNT, THEN 1 without ELSE is enough because NULL is ignored. For SUM, you should always specify ELSE 0 explicitly, otherwise a group with no matching rows can return NULL instead of 0, which distorts downstream calculations such as percentages or further sums. This small but important distinction is one of the most common beginner mistakes in conditional aggregation.

-- Conditional aggregation with CASE WHEN: multiple KPIs in one pass
SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(CASE WHEN status = 'open'      THEN 1 END)              AS open_orders,
    COUNT(CASE WHEN status = 'shipped'   THEN 1 END)              AS shipped_orders,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END)              AS cancelled_orders,
    SUM(CASE WHEN status = 'shipped'     THEN total_amount ELSE 0 END) AS shipped_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

3. The FILTER clause as a modern alternative

The SQL standard has defined the FILTER clause since SQL:2003 as a clean alternative to CASE WHEN for conditional aggregation. The syntax is AGGREGATE_FUNCTION(expression) FILTER (WHERE condition), where the condition exclusively controls which rows flow into that particular aggregate function, without nesting the actual expression inside a CASE block. PostgreSQL has fully supported FILTER since version 9.4, while MySQL and older SQL Server versions do not yet know the clause and still rely on CASE WHEN.

The advantage of FILTER over CASE WHEN in conditional aggregation lies in clarity: the condition sits directly next to the aggregate function instead of being hidden inside a nested expression, and the aggregate function itself does not need an artificial ELSE branch for NULL or 0. For COUNT(*) FILTER (WHERE status = 'shipped'), the otherwise necessary CASE construction disappears entirely, which makes the code significantly more readable, especially when many conditions are combined in one query.

-- Conditional aggregation with the FILTER clause (PostgreSQL, SQL:2003 standard)
SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) FILTER (WHERE status = 'open')      AS open_orders,
    COUNT(*) FILTER (WHERE status = 'shipped')   AS shipped_orders,
    COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_orders,
    SUM(total_amount) FILTER (WHERE status = 'shipped') AS shipped_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

4. Multiple metrics side by side in one row

The real value of conditional aggregation shows up once a report needs to compute not just three, but ten or more metrics from the same fact table. A typical example is a sales report that should show, per region, revenue, order count, new customer count, average order value, and return rate all in a single result row. Each of these metrics gets its own, independent condition, all side by side in the same SELECT list.

Without conditional aggregation, you would either need to combine these ten metrics via ten separate queries joined on a common key, or ten subqueries within the same query, each of which triggers ten independent scans of the fact table. Conditional aggregation reduces this to exactly one scan, regardless of how many metrics appear in the SELECT list, as long as they can be computed at the same grouping level.

-- Ten KPIs from one fact table scan using conditional aggregation
SELECT
    region,
    COUNT(*)                                              AS total_orders,
    COUNT(*) FILTER (WHERE is_new_customer)               AS new_customer_orders,
    SUM(total_amount)                                     AS total_revenue,
    ROUND(AVG(total_amount), 2)                           AS avg_order_value,
    COUNT(*) FILTER (WHERE returned)                      AS returned_orders,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE returned) / NULLIF(COUNT(*), 0),
        2
    )                                                      AS return_rate_pct
FROM orders
GROUP BY region
ORDER BY total_revenue DESC;

5. Avoiding NULL pitfalls in conditional aggregation

A common mistake in conditional aggregation with CASE WHEN happens when the checked column itself can contain NULL. An expression like CASE WHEN status = 'shipped' THEN 1 END returns neither true nor false when status IS NULL, but unknown, so the affected row falls into neither THEN nor an unwanted ELSE branch, but simply becomes NULL and is ignored by COUNT. In most cases this is exactly what you want, but it must be checked deliberately whenever NULL status values carry their own business meaning.

A second pitfall concerns SUM without ELSE: SUM(CASE WHEN condition THEN amount END) returns NULL instead of 0 once no row in the group satisfies the condition, which can produce unexpected NULL values in downstream calculations such as percentages. The same problem shows up with conditional aggregation via the FILTER clause, because COUNT(*) FILTER and SUM(column) FILTER follow the normal NULL behavior of their underlying aggregate function. Wrapping the overall result in COALESCE fixes both variants whenever a number instead of NULL is expected by the business logic.

-- NULL pitfalls in conditional aggregation, and how to guard against them
SELECT
    customer_id,
    -- Without ELSE: NULL when no row matches, not 0
    SUM(CASE WHEN category = 'electronics' THEN amount END) AS electronics_raw,
    -- With ELSE 0: always a number, safe for further math
    SUM(CASE WHEN category = 'electronics' THEN amount ELSE 0 END) AS electronics_safe,
    -- FILTER follows the same NULL behavior as the underlying aggregate
    COALESCE(SUM(amount) FILTER (WHERE category = 'electronics'), 0) AS electronics_filter_safe
FROM purchases
GROUP BY customer_id;

6. Conditional aggregation combined with GROUP BY

Conditional aggregation reaches its full potential only in combination with GROUP BY, because that lets conditional metrics be computed not just for the whole table, but for every group individually. A report that needs to show, per customer, the number of paid, open, and cancelled orders combines GROUP BY customer_id with several COUNT(CASE WHEN...) expressions, each with its own status condition. Every group receives its own, independently computed values for each conditional metric.

It is important not to confuse the condition inside the aggregate function with a row-level WHERE clause. A WHERE condition would remove rows entirely from the group before aggregation even starts, distorting every other metric as well. Conditional aggregation, by contrast, keeps every row of the group for the overall count, but filters selectively inside each individual aggregate function, which is the key conceptual difference between a WHERE condition and conditional aggregation via CASE WHEN or FILTER.

-- Conditional aggregation per customer group, WHERE would remove other rows entirely
SELECT
    customer_id,
    COUNT(CASE WHEN status = 'paid'      THEN 1 END) AS paid_orders,
    COUNT(CASE WHEN status = 'open'      THEN 1 END) AS open_orders,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_orders,
    COUNT(*)                                          AS total_orders
FROM orders
GROUP BY customer_id
ORDER BY total_orders DESC;

7. Computing percentages and rates from conditional counts

A particularly practical use case for conditional aggregation is computing percentages directly within the same query, without first exporting raw numbers to a reporting tool for further processing. The formula is typically 100.0 times the conditional count divided by the total count, where NULLIF protects the total count against division by zero. This combination of conditional aggregation and division is the basis of nearly every conversion or error rate in a SQL report.

On database systems that perform integer division, as can happen with older MySQL configurations, at least one operand of the division must be explicitly treated as a decimal number, usually via the 100.0 instead of a plain 100 multiplier. Without this explicit decimal point, division inside conditional aggregation returns rounded integers like 0 or 1 in such cases instead of a meaningful percentage, which in practice leads to misinterpreted dashboards.

8. Performance: one scan instead of multiple subqueries

The performance advantage of conditional aggregation over several separate queries followed by a JOIN lies in the fact that the database only needs to read the relevant table or index once, instead of once per metric. On a fact table with several million rows, the difference between a single table scan and ten separate scans followed by a JOIN can amount to several seconds of runtime, especially when no suitable index exists and every query triggers a full table scan.

Another performance aspect concerns the query planner: with conditional aggregation, the planner sees a single query with a clear GROUP BY structure and can usually execute it efficiently with a hash or sort based aggregation plan. With several separate queries followed by a subsequent JOIN, the planner additionally has to optimize the join strategy between the intermediate results, which means extra planning overhead and potentially additional sort steps that conditional aggregation avoids from the start.

9. CASE WHEN, FILTER, and separate subqueries compared

The following overview compares the three common approaches for computing multiple conditional metrics from the same table, and shows when each approach makes sense for conditional aggregation.

In practice, CASE WHEN is the most portable variant of conditional aggregation, while the FILTER clause, where available, is the clearer and often slightly more performant choice, because the query planner recognizes the filter condition directly instead of having to derive it from a CASE expression. Separate subqueries or processing in application code should be the last resort for multiple metrics from the same table.

Consistently applying conditional aggregation replaces a growing number of separate queries with a few well structured reporting queries that are easier to maintain, test, and document than a tangle of subqueries and downstream application logic.

10. Summary

Conditional aggregation combines conditions with aggregate functions to compute multiple metrics in a single pass over a table. The classic pattern uses CASE WHEN inside COUNT or SUM, where SUM needs an explicit ELSE 0 to avoid NULL instead of 0. The FILTER clause is the more modern, clearer alternative, though not available in every database system.

The biggest benefit of conditional aggregation lies in reducing table scans: instead of one query per metric, a single query with any number of conditional columns emerges. NULLIF reliably protects against division by zero in percentage calculations, and the right combination with GROUP BY delivers conditional metrics per group instead of just for the whole table.

11. FAQ: Conditional Aggregation