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:
SELECT COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count FROM users;
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.

Practice

1. What does the following SQL snippet do?
SELECT SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) FROM users;

easy

A. Counts how many users have status other than 'active'.

B. Calculates the total number of users regardless of status.

C. Returns the sum of all status values.

D. Counts how many users have status 'active'.

Solution

1. Step 1: Understand the CASE inside SUM

   The CASE returns 1 for each row where status is 'active', else 0.

2. Step 2: SUM adds all 1s and 0s

   SUM counts how many rows have status 'active' by adding 1s.

3. Final Answer:

   Counts how many users have status 'active'. -> Option D

4. Quick Check:

   SUM(CASE WHEN condition THEN 1 ELSE 0 END) counts matches [OK]

Hint: SUM with CASE counting 1s counts matching rows [OK]

Common Mistakes:

• Thinking SUM adds status values instead of counting
• Confusing ELSE 0 with ELSE NULL
• Assuming it counts non-active users

2. Which of the following is the correct syntax to count rows where score is greater than 50 using CASE inside COUNT?

easy

A. SELECT COUNT(CASE WHEN score > 50 THEN 1 END) FROM results;

B. SELECT COUNT(CASE score > 50 THEN 1 ELSE 0 END) FROM results;

C. SELECT COUNT(CASE WHEN score > 50 THEN 1 ELSE 0 END) FROM results;

D. SELECT COUNT(CASE score > 50 THEN 1 END) FROM results;

Solution

1. Step 1: Recall COUNT counts non-null values

   COUNT counts rows where expression is NOT NULL.

2. Step 2: Use CASE to return 1 for matches, NULL otherwise

   CASE WHEN condition THEN 1 END returns 1 or NULL (default ELSE NULL).

3. Final Answer:

   SELECT COUNT(CASE WHEN score > 50 THEN 1 END) FROM results; -> Option A

4. Quick Check:

   COUNT with CASE returning NULL for no match counts only matches [OK]

Hint: COUNT counts non-null, so ELSE NULL is default [OK]

Common Mistakes:

• Using ELSE 0 causes COUNT to count all rows
• Omitting WHEN keyword
• Using invalid CASE syntax

3. Given the table sales with columns region and amount, what is the output of this query?

SELECT region, SUM(CASE WHEN amount > 100 THEN amount ELSE 0 END) AS big_sales FROM sales GROUP BY region;

medium

A. Sum of all sales amounts per region.

B. Sum of sales amounts greater than 100 per region.

C. Count of sales greater than 100 per region.

D. Sum of sales amounts less than or equal to 100 per region.

Solution

1. Step 1: Analyze CASE inside SUM

   CASE returns amount if > 100, else 0, so only big sales count fully.

2. Step 2: SUM adds these amounts per region

   SUM totals amounts > 100 per region, ignoring smaller sales by adding 0.

3. Final Answer:

   Sum of sales amounts greater than 100 per region. -> Option B

4. Quick Check:

   SUM with CASE filters values conditionally [OK]

Hint: SUM with CASE ELSE 0 sums only matching values [OK]

Common Mistakes:

• Thinking it counts rows instead of sums amounts
• Ignoring ELSE 0 and expecting NULL filtering
• Confusing greater than with less than

4. Identify the error in this query:

SELECT department, COUNT(CASE department = 'HR' THEN 1 ELSE NULL END) FROM employees GROUP BY department;

medium

A. Missing WHEN keyword in CASE statement.

B. COUNT cannot be used with CASE.

C. GROUP BY should include COUNT column.

D. ELSE NULL is invalid in CASE.

Solution

1. Step 1: Check CASE syntax

   CASE requires WHEN before condition; here it's missing.

2. Step 2: Confirm correct syntax

   Correct form: CASE WHEN department = 'HR' THEN 1 ELSE NULL END.

3. Final Answer:

   Missing WHEN keyword in CASE statement. -> Option A

4. Quick Check:

   CASE syntax needs WHEN before condition [OK]

Hint: CASE must have WHEN before condition [OK]

Common Mistakes:

• Omitting WHEN keyword
• Misusing COUNT with CASE
• Incorrect GROUP BY usage

5. You want to find the average salary of employees, but only include salaries above 50000. Which query correctly uses CASE with AVG to achieve this?

hard

A. SELECT AVG(CASE WHEN salary > 50000 THEN salary ELSE 0 END) FROM employees;

B. SELECT AVG(CASE salary > 50000 THEN salary END) FROM employees;

C. SELECT AVG(CASE WHEN salary > 50000 THEN salary ELSE NULL END) FROM employees;

D. SELECT AVG(CASE salary > 50000 THEN salary ELSE NULL END) FROM employees;

Solution

1. Step 1: Understand AVG ignores NULLs

   AVG calculates average of non-null values only.

2. Step 2: Use CASE to return salary if > 50000, else NULL

   This excludes salaries ≤ 50000 from average calculation.

3. Final Answer:

   SELECT AVG(CASE WHEN salary > 50000 THEN salary ELSE NULL END) FROM employees; -> Option C

4. Quick Check:

   AVG with CASE ELSE NULL excludes unwanted rows [OK]

Hint: Use ELSE NULL to exclude rows from AVG calculation [OK]

Common Mistakes:

• Using ELSE 0 includes zeros in average, skewing result
• Omitting WHEN keyword
• Using invalid CASE syntax