CASE with aggregate functions in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the CASE statement do when used with aggregate functions in SQL?

It allows you to apply conditional logic inside aggregate functions, so you can count or sum values based on specific conditions.

beginner

Write a simple example of using CASE inside a COUNT aggregate function.

Example: <br>SELECT COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count FROM users;<br>This counts only rows where status is 'active'.

intermediate

Why use CASE inside SUM or COUNT instead of filtering with WHERE?

CASE lets you count or sum different conditions in the same query without filtering out rows, so you get multiple results in one query.

intermediate

How does the CASE statement handle ELSE in aggregate functions?

ELSE defines what value to use when no conditions match. If ELSE is omitted, NULL is used, which aggregate functions usually ignore.

intermediate

Can you use multiple WHEN conditions inside a single CASE for aggregate functions?

Yes, you can have multiple WHEN conditions to handle different cases and assign values accordingly inside aggregate functions.

What does this SQL do? SELECT SUM(CASE WHEN score > 50 THEN 1 ELSE 0 END) FROM tests;

A Sums all scores

B Sums all scores above 50

C Counts all scores below 50

D Counts how many scores are above 50

The CASE returns 1 for scores above 50 and 0 otherwise, so SUM counts how many scores are above 50.

Which aggregate function ignores NULL values by default?

A COUNT

B AVG

C All of the above

D SUM

COUNT, SUM, and AVG all ignore NULL values when calculating results.

What happens if you omit ELSE in a CASE inside an aggregate function?

A It treats unmatched cases as NULL

B It causes an error

C It treats unmatched cases as 0

D It treats unmatched cases as 1

Without ELSE, CASE returns NULL for unmatched cases, which aggregate functions ignore.

Why use CASE inside aggregate functions instead of WHERE clause?

A To filter rows before aggregation

B To apply conditions inside aggregation and get multiple results in one query

C To speed up the query

D To join tables

CASE inside aggregates lets you count or sum different conditions in one query without filtering rows out.

Which is a valid use of CASE with COUNT?

A COUNT(CASE WHEN age > 18 THEN 1 END)

B COUNT(CASE age > 18 THEN 1 END)

C COUNT(CASE WHEN age > 18 THEN END)

D COUNT(CASE age > 18)

The correct syntax uses WHEN and THEN inside CASE, like COUNT(CASE WHEN condition THEN value END).

Explain how to use CASE inside aggregate functions to count conditional values in SQL.

Think about counting only rows that meet a condition without filtering the whole query.

Describe the difference between filtering rows with WHERE and using CASE inside aggregate functions.

Consider when you want multiple counts or sums in one query versus just filtering rows.