SQL conditional aggregation: calculating multiple metrics from the same data

In P04 and P05, 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 the requirement is:

One approach would be to write several separate queries, such as this one, then another for pending, then another for cancelled:

SELECT COUNT(*)
FROM orders
WHERE status = 'paid';

This works, but SQL can calculate all of these metrics together. That is where conditional aggregation becomes useful.

One common conditional aggregation pattern is SUM(CASE WHEN condition THEN 1 ELSE 0 END). Suppose the requirement is:

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. Each order’s value is quantity * unit_price, and we want the total only for paid orders:

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).

Multiple metrics from the same data

Now we can combine several business metrics in one query. Suppose a dashboard needs:

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 becomes even more useful when combined with GROUP BY. Suppose the requirement is:

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 this query:

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.

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.

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.

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)

For conditional counts, either pattern can be correct. Many teams prefer the SUM form because the 1 and 0 make the counting logic easy to understand. 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. Suppose the requirement is:

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.

Some databases support FILTER, a shorter way to calculate aggregates for rows that meet a specific condition.

For example, both expressions count paid orders:

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

COUNT(*) FILTER (WHERE status = 'paid')

The FILTER condition applies only to that aggregate, leaving other calculations in the query unaffected.

You can also use it with SUM:

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

GROUP BY and conditional aggregation can summarise the same data in different ways. The difference is how the results are organised.

Using GROUP BY

Suppose we want to count orders by their status.

Grouping by status returns one row for each status.

We get three rows because there are three different statuses.

Using Conditional Aggregation

Now suppose we want the number of paid, pending, and cancelled orders in a single row. Conditional aggregation lets us calculate each count separately and return them as columns.

The counts are the same, but the result contains one row with three columns.

Can we use both together?

Yes. We can combine GROUP BY with conditional aggregation.

For example, GROUP BY customer gives us one row per customer. Within each group, conditional aggregation calculates separate counts for paid and pending orders. This is what we did in Section 03.

Remember: GROUP BY determines which rows belong together. Conditional aggregation determines which rows contribute to each metric. We can use both when we need several metrics for each group.

Before writing a conditional aggregate, answer these four questions.

1. What should each result row represent?
Do we need one summary row for all orders or one row per customer? Use GROUP BY when the result needs separate rows for each group.

2. What do we want to calculate?
For example, we want to count the number of paid orders.

3. Which rows should contribute?
Only orders where status = 'paid' should contribute to the paid order count.

4. What should other rows contribute?
This depends on the aggregate:

• With SUM(CASE), matching rows contribute 1, and other rows contribute 0.
• With COUNT(CASE), matching rows produce 1, and other rows produce NULL, which COUNT ignores.

Remember: First decide what each result row represents. Then choose the metric, its condition, and what non-matching rows should contribute.

Consider these orders, with each order’s amount already calculated:

-- Option A
SELECT customer,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
    SUM(CASE WHEN status = 'pending' THEN order_amount END) AS pending_value
FROM orders
GROUP BY customer;
 
-- Option B
SELECT customer,
    COUNT(*) AS paid_orders,
    SUM(CASE WHEN status = 'pending' THEN order_amount ELSE 0 END) AS pending_value
FROM orders
WHERE status = 'paid'
GROUP BY customer;
 
-- Option C
SELECT customer,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
    SUM(CASE WHEN status = 'pending' THEN order_amount ELSE 0 END) AS pending_value
FROM orders
GROUP BY customer;
 
-- Option D
SELECT customer, status,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
    SUM(CASE WHEN status = 'pending' THEN order_amount ELSE 0 END) AS pending_value
FROM orders
GROUP BY customer, status;

Conditional aggregation is useful when a report needs multiple metrics from the same data. For example, you might need to:

• Count paid, pending, and cancelled orders in one report.
• Calculate revenue separately for different order statuses.
• Compare these metrics across customers, products, or stores.

Instead of writing separate queries for each condition, conditional aggregation lets you calculate these metrics together.

Common Mistakes

A query can run successfully and still return incorrect totals. Watch for these mistakes:

1. Using ELSE 0 inside COUNT(CASE)
COUNT includes every non-NULL value, including 0. Adding ELSE 0 makes non-matching rows count too.

2. Missing ELSE 0 in SUM(CASE)
Without ELSE, non-matching rows contribute NULL. If a group has rows but none match the condition, the sum returns NULL instead of 0. Use ELSE 0 when zero is the intended result.

3. Filtering rows too early
A WHERE condition removes rows before aggregation. If a report needs metrics for multiple statuses, filtering to one status can exclude rows needed by other calculations.

4. Using COUNT(column) to count all rows
COUNT(column) ignores rows where that column is NULL. Use COUNT(*) when every row should count.

Key Takeaways

• Count matching rows: Use SUM(CASE WHEN ... THEN 1 ELSE 0 END) or COUNT(CASE WHEN ... THEN 1 END).
• Calculate conditional totals: Use SUM(CASE WHEN ... THEN amount ELSE 0 END) to sum values for matching rows.
• Understand GROUP BY: It determines what each result row represents, while conditional aggregation calculates metrics within that group.
• Know CASE vs. WHERE: CASE can control which values contribute to an aggregate. WHERE controls which rows are available to the entire query.
• Know your SQL dialect: FILTER offers a shorter alternative in supported databases, while CASE works across more SQL systems.

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, in P07 we will move from summarising one table to combining related data across tables using SQL joins.