Aggregation with CASE
intermediateaggregation
2 min read
What is Aggregation with CASE?
By placing a CASE expression inside an aggregate function, you can conditionally include or exclude values. This technique is sometimes called conditional aggregation and lets you pivot data or compute multiple metrics in one query.
Syntax
SELECT
SUM(CASE WHEN condition THEN value ELSE 0 END) AS conditional_sum,
COUNT(CASE WHEN condition THEN 1 END) AS conditional_count
FROM table_name;When to Use
- Counting or summing different subsets in a single query
- Creating pivot-table-like results
- Computing percentages within groups
Key Points
- Inside Any Aggregate — CASE works inside COUNT, SUM, AVG, MIN, MAX, etc.
- COUNT Trick — COUNT only counts non-NULL values, so
COUNT(CASE WHEN x THEN 1 END)counts matching rows. - FILTER Alternative — PostgreSQL's FILTER clause is a cleaner alternative (PostgreSQL 9.4+).
- ELSE Matters — For SUM, use ELSE 0. For COUNT, omit ELSE (NULL is not counted).
- Multiple Metrics — You can have many CASE aggregates in one SELECT.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
Aggregation with CASE Challenge
Write a query that solve this task: count grade-A and grade-B students in a single query.
Expected result
Three columns: count of A students, count of B students, and total.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is Aggregation with CASE?
Initializing database...Each run starts from fresh sample data.
More Examples
Conditional sum of order revenue
Sum revenue for high-value and low-value orders.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Should I use CASE or FILTER for conditional aggregation?
In PostgreSQL, FILTER is cleaner and recommended. CASE is more portable across databases.
Why use ELSE 0 in SUM but not in COUNT?
SUM(NULL) is NULL, so ELSE 0 prevents NULLs. COUNT skips NULLs naturally, so omitting ELSE (which defaults to NULL) makes COUNT ignore non-matching rows.