Segmented Metrics with CASE Expressions and Conditional Aggregation

Hello! This module develops the PostgreSQL techniques used to turn detailed records into analytical reports. We’ll begin with a common request: “Show each region’s total orders, paid orders, and paid revenue.” The challenge is that those metrics use different rules but must appear together in the same report.

By the end of this lesson, you’ll be able to define segments with CASE, calculate conditional counts and sums, and check whether the resulting metrics mean what you intend. You’ll need only the basic ideas of SELECT, SUM, and GROUP BY; we’ll connect them as we go.

---

Give each row a business meaning

A CASE expression evaluates conditions in order and returns the result for the first condition that is true. That makes it useful for turning a numeric field into reporting categories.

Suppose an order has an amount. We can assign it a band without changing the stored data:

CASE
    WHEN amount IS NULL THEN 'Unknown amount'
    WHEN amount >= 100 THEN '100 or more'
    ELSE 'Under 100'
END

An amount of 100 belongs in 100 or more, while 99.99 belongs in Under 100. The first condition keeps a missing amount out of the latter category: without it, a NULL comparison would not be true, and the row would reach ELSE. In a business report, “unknown” and “under 100” should not silently become the same thing.

Here is a small PostgreSQL dataset we’ll use throughout the lesson. Run the setup if you have a SQL environment, or use the nine rows to trace the queries on paper.

CREATE TEMP TABLE orders_demo (
    order_id integer PRIMARY KEY,
    region text,
    status text,
    amount numeric(10, 2)
);

INSERT INTO orders_demo (order_id, region, status, amount) VALUES
    (1, 'North', 'paid',      100.00),
    (2, 'North', 'paid',       50.00),
    (3, 'North', 'cancelled',  40.00),
    (4, 'North', 'pending',    70.00),
    (5, 'South', 'paid',       80.00),
    (6, 'South', 'cancelled',  20.00),
    (7, 'South', 'paid',        NULL),
    (8, 'South', 'pending',    30.00),
    (9, 'South', NULL,         10.00);

The intended business rule is that only paid orders contribute to paid revenue. Notice two different kinds of missing information: order 7 is paid but has no recorded amount; order 9 has an amount but no known status. Neither should be mistaken for a normal zero-value paid order.

---

Put the rule inside the aggregate

GROUP BY region produces one result row per region. An ordinary COUNT(*) counts every source row in that region. To count only paid rows without removing the other rows, place a CASE inside an aggregate:

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

The CASE contributes 1 for a paid order and 0 otherwise; SUM adds those flags within each region. Another valid counting pattern is:

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

Here, nonmatching rows produce NULL because there is no ELSE, and COUNT(expression) ignores NULL. Do not add ELSE 0 to this COUNT pattern: COUNT counts zero as a non-null value.

Watch the worked example before combining several metrics:

For a compact reference, read the basic count and sum patterns:

Now we can answer the regional reporting request:

SELECT
    region,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_orders,
    SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_revenue
FROM orders_demo
GROUP BY region
ORDER BY region;

region	total_orders	paid_orders	cancelled_orders	paid_revenue
North	4	2	1	150.00
South	5	2	1	80.00

Read across the North row: it contains four orders in total, two of which are paid. Only the amounts 100.00 and 50.00 enter its paid-revenue sum. South also has two paid orders, but only one has a known amount, so its recorded paid revenue is 80.00. We will return to that qualification shortly.

The important structure is that every aggregate works at the same grain—one row per region—but each conditional aggregate applies its own rule to the rows within that region.

---

Choose between grouping into segments and showing segments side by side

There are two related ways to produce a segmented report.

In the regional report, region determines the result rows, while conditions such as status = 'paid' determine particular metric columns. Alternatively, a CASE expression itself can determine the result rows. For example, this query counts orders by amount band:

SELECT
    CASE
        WHEN amount IS NULL THEN 'Unknown amount'
        WHEN amount >= 100 THEN '100 or more'
        ELSE 'Under 100'
    END AS amount_band,
    COUNT(*) AS orders
FROM orders_demo
GROUP BY
    CASE
        WHEN amount IS NULL THEN 'Unknown amount'
        WHEN amount >= 100 THEN '100 or more'
        ELSE 'Under 100'
    END
ORDER BY amount_band;

The results are one order at 100 or more, seven Under 100, and one with Unknown amount. The CASE appears in both SELECT and GROUP BY because it defines the category being displayed and the category whose rows are combined.

A WHERE clause serves a different purpose. If you put WHERE status = 'paid' before the regional aggregation, cancelled and pending orders disappear from the input. COUNT(*) would then mean “paid orders,” not “all orders,” and a cancelled-order metric would be zero in every region. Use WHERE when you truly want to restrict the whole report—for example, to a particular reporting period. Use a condition inside an aggregate when only that metric should be restricted.

---

Protect the meaning of a metric

NULL does not automatically mean zero. In our data, order 7 is known to be paid, so it belongs in paid_orders. Its amount is unknown, however, so it cannot add a known value to paid_revenue.

There is also an important technical distinction:

• SUM(CASE WHEN condition THEN amount END) returns NULL when there are no non-null values to sum.
• SUM(CASE WHEN condition THEN amount ELSE 0 END) contributes zero for nonmatching rows in a nonempty group, but a matching row whose amount is NULL still contributes NULL.
• COALESCE(SUM(...), 0) can turn a resulting NULL into zero when zero is the intended reporting value. It does not recover an unknown amount.

Read the source’s discussion, keeping that last business distinction in mind:

Suppose the requested rate is the percentage of all orders that are paid, by region. The numerator is paid orders; the denominator is all orders in that region. We can also expose missing paid amounts so that the revenue figure is not reported without context:

SELECT
    region,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
    SUM(
        CASE WHEN status = 'paid' AND amount IS NULL THEN 1 ELSE 0 END
    ) AS paid_orders_missing_amount,
    ROUND(
        100.0 * SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)
        / NULLIF(COUNT(*), 0),
        1
    ) AS paid_order_pct
FROM orders_demo
GROUP BY region
ORDER BY region;

region	total_orders	paid_orders	paid_orders_missing_amount	paid_order_pct
North	4	2	0	50.0
South	5	2	1	40.0

100.0 produces a percentage with a fractional part. NULLIF(COUNT(*), 0) prevents division by zero if this calculation is later adapted to a result set that can include empty groups; the two groups in this example are nonempty.

The South rate is 2 out of 5, not 2 out of 4: the order with unknown status remains in the stated denominator, “all orders.” If a stakeholder instead means “percentage of orders with known status that are paid,” that is a different metric with a different denominator. The SQL should follow the definition, not decide it accidentally.

Before trusting a segmented report, perform a simple reasonableness check: do its counts reconcile with the source rows, and do boundaries and missing values land where you expect? Here, North has two paid, one cancelled, and one pending order, totaling four. South has two paid, one cancelled, one pending, and one with unknown status, totaling five. The missing paid amount is visible rather than disguised as recorded revenue.

---

Key takeaways

A CASE expression assigns a result to each row; inside SUM or COUNT, it lets one query calculate several metrics with different conditions. GROUP BY sets the result grain, while a condition inside an aggregate restricts just that metric. For a trustworthy business report, define the population, check NULL behavior, and state the denominator of every rate.

Next, you’ll use non-correlated subqueries when a metric needs an intermediate result set rather than just conditional calculations over the current groups.