Conditional Aggregation (CASE inside Aggregates) in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this CASE WHEN Expressions, GROUP BY
Builds toward FILTER Clause on Aggregates, Multi-CTE Query Architecture
What is Conditional Aggregation in SQL?
Conditional aggregation puts a CASE WHEN expression inside an aggregate function to compute separate metrics — filtered differently — in a single query pass.
You're building an order status summary per customer. You want one row per customer with columns for delivered count, pending count, and cancelled count. Without conditional aggregation, you'd need three separate queries: one for delivered, one for pending, one for cancelled. With conditional aggregation, one query computes all three by combining CASE WHEN with COUNT or SUM inside GROUP BY.
How does CASE inside COUNT work in SQL?
The idea: CASE WHEN inside an aggregate returns a value when the condition matches and NULL when it doesn't. Aggregate functions skip NULLs. So COUNT(CASE WHEN status = 'delivered' THEN 1 END) counts only delivered orders — the CASE returns 1 for delivered rows and NULL for everything else, and COUNT skips the NULLs.
How do you pivot rows into columns in one SQL query?
Here's the three-column status breakdown per customer:
SELECT customer_id,
COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered_count,
COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_count,
COUNT(CASE WHEN status <> 'delivered' THEN 1 END) AS not_delivered_count
FROM orders
GROUP BY customer_idSQL scans the orders table once. For each row it evaluates the CASE expressions, passing the result into the respective COUNT. One pass, three conditional counts.
How do you sum only the rows that match a condition?
SUM works the same way — conditional revenue totals:
SELECT
SUM(CASE WHEN status = 'delivered' THEN total_amount END) AS delivered_revenue,
SUM(CASE WHEN status = 'cancelled' THEN total_amount END) AS cancelled_revenue
FROM ordersSUM totals the total_amount for delivered orders and NULL for everything else — the NULLs get skipped. AVG follows the same logic: it averages only the rows where the condition matches.
SELECT customer_id, COUNT(*) AS total_orders, COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered_orders FROM orders GROUP BY customer_id
Compare COUNT(*) against the conditional count to see how many of each customer's orders were delivered.
Why does adding ELSE 0 break a conditional count?
The one thing that trips people up: omitting ELSE and not realizing it's intentional.
When a CASE WHEN inside an aggregate has no ELSE, unmatched rows produce NULL — and aggregates skip NULLs. That behavior is the whole mechanism. An ELSE clause would change what gets counted or summed. COUNT(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) counts every row because 0 is not NULL. Remove the ELSE and only delivered rows contribute. Keep the ELSE clause out unless you specifically want to count or sum non-matching rows.
CASE WHEN status = 'delivered' THEN total_amount END returns NULL for non-delivered rows. Why does this make it useful inside SUM?
Practice Conditional Aggregation in SQL
Brightlane's finance team wants a single-row summary covering two figures:
- The combined revenue from delivered orders (
status = 'delivered'). - The combined revenue from cancelled orders (
status = 'cancelled').
Write a query to return both totals in a single row.
Assumptions:
- The
orderstable contains every order Brightlane has processed. - Both totals come from the same scan of the
orderstable — noWHEREclause restricts rows out, because both buckets are needed in the same row.
Output:
- A single row with two columns,
delivered_revenueandcancelled_revenue.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 Conditional Aggregation practice problems
Write a query to return both totals in a single row.
Write a query to return three columns per customer: their ID, their count of delivered orders, and their count of orders in any other status.
Write a query to return the department ID, the active count, and the inactive count.
Write a query to return all three alongside the customer ID, in a single row per customer.
Write a query to return all three columns per department.
Write a query to return both averages in a single row.
Write a query to return three columns per order: the order ID, the high-value total, and the standard total.
Write a query to return the customer ID and the average delivered-order value for every customer who has any orders on file.
Write a query to return all four columns per customer.
Start learning to practice all 9 Conditional Aggregation problems, with instant grading and mastery tracking.
Deeper guides on Conditional Aggregation
- counting subsets with CASE inside an aggregate
One aggregate with a FILTER clause per column, with no extension to install.
Common questions about Conditional Aggregation
Does conditional aggregation need a GROUP BY?
No. Without one the whole table is a single group, so you still get one row holding a count of everything beside a count of only the matching rows. GROUP BY is what turns that single summary row into one per category.
Can you use AVG conditionally as well as COUNT?
Yes, and the mechanism is identical. A CASE that yields a value only for matching rows leaves NULL everywhere else, and AVG skips NULLs, so the average is taken over the matching rows alone rather than over the whole table.
What is the difference between a conditional count and a filtered query?
A filtered query answers one question and throws the other rows away. Conditional aggregation answers several at once over the same rows, which is how you get delivered, pending and cancelled counts side by side instead of running three queries and lining them up by hand.
