SQL conditional aggregation: calculating multiple metrics from the same data

Aggregating different subsets of the same data

In the previous articles, we learned two important ideas. GROUP BY lets us summarise multiple rows, and CASE lets us produce different values depending on a condition. Conditional aggregation combines these ideas: it allows us to calculate an aggregate using only the rows that satisfy a particular condition. Consider the same orders table:

Suppose we need a summary showing the total number of orders, the number of paid, pending, and cancelled orders, and the total value of paid orders. One approach would be to write several separate queries, such as SELECT COUNT(*) FROM orders WHERE status = 'paid';, then another for pending, then another for cancelled. This works, but SQL can calculate all of these metrics together. That is where conditional aggregation becomes useful.

Counting rows with SUM(CASE...)

One common conditional aggregation pattern is SUM(CASE WHEN condition THEN 1 ELSE 0 END). Suppose we want to count paid orders:

SELECT
    SUM(
        CASE
            WHEN status = 'paid' THEN 1
            ELSE 0
        END
    ) AS paid_order_count
FROM orders;

1 + 0 + 1 + 0 + 1 + 1 = 4  →  paid_order_count

Calculating several counts in one query

Once we understand the pattern, we can calculate several metrics from the same rows:

SELECT
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'paid'      THEN 1 ELSE 0 END) AS paid_orders,
    SUM(CASE WHEN status = 'pending'   THEN 1 ELSE 0 END) AS pending_orders,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders
FROM orders;

The table is read as one logical input set, while each aggregate applies its own condition. This is one of the main reasons conditional aggregation is useful: we can calculate several related metrics in the same result row.

Conditional SUM: adding values only when a condition matches

Conditional aggregation is not limited to counting rows. Suppose we want the total value of paid orders. The value of each order is quantity * unit_price, and we want that amount to contribute only when status = 'paid':

SELECT
    SUM(
        CASE
            WHEN status = 'paid'
                THEN quantity * unit_price
            ELSE 0
        END
    ) AS paid_order_value
FROM orders;

1500 + 0 + 3600 + 0 + 750 + 3600 = 9450  →  paid_order_value

The pattern is SUM(CASE WHEN condition THEN value ELSE 0 END): for matching rows, contribute the value; for non-matching rows, contribute zero.

Multiple metrics from the same data

Now we can combine several business metrics in one query, such as a dashboard needing total orders, paid, pending, and cancelled counts, total order value, paid order value, and pending order value:

SELECT
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'paid'      THEN 1 ELSE 0 END) AS paid_orders,
    SUM(CASE WHEN status = 'pending'   THEN 1 ELSE 0 END) AS pending_orders,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders,
    SUM(quantity * unit_price) AS total_order_value,
    SUM(CASE WHEN status = 'paid'
             THEN quantity * unit_price ELSE 0 END) AS paid_order_value,
    SUM(CASE WHEN status = 'pending'
             THEN quantity * unit_price ELSE 0 END) AS pending_order_value
FROM orders;

One query now produces several metrics describing the same underlying data.

Conditional aggregation with GROUP BY

Conditional aggregation becomes even more useful when combined with GROUP BY. “For each customer, show total orders, paid orders, pending orders, and total paid order value”:

SELECT
    customer,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'paid'    THEN 1 ELSE 0 END) AS paid_orders,
    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders,
    SUM(CASE WHEN status = 'paid'
             THEN quantity * unit_price ELSE 0 END) AS paid_order_value
FROM orders
GROUP BY customer
ORDER BY customer;

The result grain is still one row per customer, but each row now contains several measures calculated from different subsets of that customer’s orders. For Ben, order 1002 is pending (3200) and 1006 is paid (3600), so total_orders = 2, paid_orders = 1, pending_orders = 1, paid_order_value = 3600.

Why not put the condition in WHERE?

Consider SELECT customer, COUNT(*) AS paid_orders FROM orders WHERE status = 'paid' GROUP BY customer;. This correctly counts paid orders. But the WHERE clause removes every non-paid order before aggregation begins, so we cannot simultaneously calculate paid, pending, and cancelled counts from the same input rows. For example, WHERE status = 'paid' removes Ben’s pending order before any aggregates are calculated. Conditional aggregation keeps the row available; it simply contributes differently to each metric.

COUNT(CASE WHEN...)

There is another common way to perform conditional counting: COUNT(CASE WHEN condition THEN 1 END). Why does this work? Remember that COUNT(expression) counts non-NULL values. The CASE expression produces paid → 1 and not paid → NULL, because there is no ELSE:

SELECT
    COUNT(
        CASE
            WHEN status = 'paid' THEN 1
        END
    ) AS paid_orders
FROM orders;

COUNT counts the four non-NULL results, so the query returns 4.

The COUNT(CASE...) that counts every row

Suppose someone writes the query below. At first glance, this seems reasonable: for paid orders 1, for everything else 0, so you might expect COUNT to count only the ones. It does not.

SELECT
    COUNT(
        CASE
            WHEN status = 'paid' THEN 1
            ELSE 0
        END
    ) AS paid_orders
FROM orders;

The fix

If you want to use COUNT(CASE...), allow non-matching rows to become NULL by dropping the ELSE. You could also explicitly write ELSE NULL, but that is unnecessary because NULL is already the default. Alternatively, use the SUM pattern. Both correctly return 4:

COUNT(CASE WHEN status = 'paid' THEN 1 END)
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)

SUM(CASE...) or COUNT(CASE...), and empty sums

For conditional counts, either pattern can be correct. Many teams prefer the SUM form because the 1 and 0 make the counting logic visually explicit. The most important thing is not which style you choose; it is understanding why it works.

Conditions can use more than one column

The condition inside CASE can be as specific as the business requirement requires. “Total value of paid orders worth at least 3,000”:

SELECT
    SUM(
        CASE
            WHEN status = 'paid'
             AND quantity * unit_price >= 3000
                THEN quantity * unit_price
            ELSE 0
        END
    ) AS high_value_paid_order_value
FROM orders;

The qualifying orders are 1003 → 3600 and 1006 → 3600, so the result is 7200. Conditional aggregation can use the same logical conditions we already learned with WHERE and CASE.

Using FILTER

Some SQL databases support the FILTER clause for aggregates. Instead of a nested CASE, you can write:

SUM(
    CASE
        WHEN status = 'paid'
            THEN quantity * unit_price
        ELSE 0
    END
)

SUM(quantity * unit_price)
    FILTER (WHERE status = 'paid')

Similarly, instead of COUNT(CASE WHEN status = 'paid' THEN 1 END), you can write COUNT(*) FILTER (WHERE status = 'paid'). This makes the intent very clear: calculate this aggregate using only rows that satisfy this condition. Using FILTER, our earlier summary becomes:

SELECT
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 'paid')      AS paid_orders,
    COUNT(*) FILTER (WHERE status = 'pending')   AS pending_orders,
    COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_orders,
    SUM(quantity * unit_price) FILTER (WHERE status = 'paid') AS paid_order_value
FROM orders;

Conditional aggregation is not the same as GROUP BY

Suppose we want one row per status. GROUP BY status creates several result rows. Conditional aggregation solves a different presentation problem, producing several status-specific metrics as columns in the same result row.

Both queries summarise the same data, but their result shapes are different. Use GROUP BY status when you want one row per status; use conditional aggregation when you want several status-specific metrics as columns in the same result row.

When conditional aggregation is useful

Look for common mistakes such as:

Build a conditional aggregate from four questions

One more prediction

Consider these orders. Requirement: return the total number of orders and the number of paid orders in the same result row. Which query is correct?

-- Option A
SELECT COUNT(*) AS total_orders,
    COUNT(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders
FROM orders;

-- Option B
SELECT COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders
FROM orders;

-- Option C
SELECT COUNT(*) AS total_orders, COUNT(*) AS paid_orders
FROM orders WHERE status = 'paid';

-- Option D
SELECT status, COUNT(*) AS total_orders
FROM orders GROUP BY status;

Lock these in

Summary

Conditional aggregation combines conditional logic with aggregate functions. For conditional counts, a common pattern is SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END); for conditional sums, SUM(CASE WHEN status = 'paid' THEN quantity * unit_price ELSE 0 END). You can calculate several metrics from the same data, and combine conditional aggregation with GROUP BY. If your database supports FILTER, expressions such as COUNT(*) FILTER (WHERE status = 'paid') can provide a cleaner alternative.

Instead of removing rows globally, decide how each row should contribute to each metric. Next, we will move from summarising one table to combining related data across tables using SQL joins.