Conditional aggregation pattern in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the conditional aggregation pattern in SQL?

It is a way to calculate sums, counts, or other aggregates based on conditions inside a single query, using CASE statements inside aggregate functions.

beginner

How do you count rows that meet a condition using conditional aggregation?

Use COUNT(CASE WHEN condition THEN 1 END) or SUM(CASE WHEN condition THEN 1 ELSE 0 END) to count only rows that satisfy the condition.

intermediate

Why use conditional aggregation instead of multiple queries?

It combines multiple conditional counts or sums into one query, making it faster and easier to read, like counting different categories in one go.

beginner

Write a simple example of conditional aggregation to count male and female employees.

SELECT COUNT(CASE WHEN gender = 'M' THEN 1 END) AS male_count, COUNT(CASE WHEN gender = 'F' THEN 1 END) AS female_count FROM employees;

beginner

What happens if the CASE condition is false in conditional aggregation?

The CASE returns NULL or 0 (if ELSE 0 is used), so the aggregate function ignores or does not add that row to the count or sum.

Which SQL function is commonly used with CASE for conditional aggregation?

A SUM

B JOIN

C ORDER BY

D GROUP BY

SUM is often combined with CASE to add values conditionally in one query.

How do you count rows where a condition is true using conditional aggregation?

A WHERE condition

B COUNT(CASE WHEN condition THEN 1 END)

C GROUP BY condition

D ORDER BY condition

COUNT with CASE counts only rows where the condition is true.

What does this SQL snippet do? SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END)

A Sums all rows

B Counts inactive rows

C Counts active rows

D Filters rows

It sums 1 for each active row, effectively counting them.

Why is conditional aggregation useful?

A To combine multiple conditional counts or sums in one query

B To delete rows

C To create new tables

D To sort data

It helps summarize data by different conditions efficiently.

What will happen if CASE returns NULL inside an aggregate function like COUNT?

A The query fails

B NULL is counted as zero

C The row is counted anyway

D That row is ignored in the count

COUNT ignores NULL values, so those rows are not counted.

Explain how to use the conditional aggregation pattern to count different categories in one SQL query.

Think about how CASE can return values only when conditions are met.

Describe the difference between filtering rows with WHERE and counting conditionally with CASE inside aggregation.

Consider when rows are included or excluded in each method.