Conditional Aggregation (CASE inside Aggregates) in SQL
Conditional aggregation places a CASE WHEN expression inside an aggregate function, letting a single GROUP BY query compute separate aggregate values for different subsets of rows simultaneously.
Before this CASE WHEN Expressions, GROUP BY
Builds toward FILTER Clause on Aggregates, Multi-CTE Query Architecture
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.
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.
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.
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.
Compare COUNT(*) against the conditional count to see how many of each customer's orders were delivered.
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?
9 Conditional Aggregation (CASE inside Aggregates) 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.
These problems are part of the Conditional Aggregation (CASE inside Aggregates) lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.