CASE with aggregate functions in SQL - Step-by-Step Execution

Concept Flow - CASE with aggregate functions

Start Query

↓

Aggregate Function Calculates

↓

CASE Evaluates Condition on Aggregate Result

↓

WHEN condition True

→Return Value 1

↓

WHEN condition False

→Return Value 2

↓

Output Final Result

↓

End

The query first calculates an aggregate value, then the CASE statement checks conditions on that value to decide what output to return.

Execution Sample

SQL

SELECT
  department_id,
  CASE
    WHEN AVG(salary) > 5000 THEN 'High'
    ELSE 'Low'
  END AS salary_level
FROM employees
GROUP BY department_id;

This query groups employees by department, calculates average salary per department, and labels it 'High' or 'Low' based on the average.

Execution Table

Step	department_id	AVG(salary)	CASE Condition (AVG(salary) > 5000)	CASE Result	Output Row
1	10	6000	6000 > 5000 = True	'High'	(10, 'High')
2	20	4500	4500 > 5000 = False	'Low'	(20, 'Low')
3	30	5200	5200 > 5000 = True	'High'	(30, 'High')
4	40	3000	3000 > 5000 = False	'Low'	(40, 'Low')
5	End				Query complete, all groups processed

💡 All department groups processed, query ends.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	Final
department_id	N/A	10	20	30	40	N/A
AVG(salary)	N/A	6000	4500	5200	3000	N/A
CASE Result	N/A	'High'	'Low'	'High'	'Low'	N/A

Key Moments - 2 Insights

Why does the CASE statement use the aggregate AVG(salary) instead of individual salaries?

Because the query groups rows by department_id, the aggregate AVG(salary) calculates one average per group. The CASE evaluates this single value per group, not individual salaries. See execution_table rows 1-4.

What happens if AVG(salary) equals exactly 5000?

The CASE condition is AVG(salary) > 5000, so if AVG(salary) is 5000, the condition is False and the ELSE branch 'Low' is returned. This is shown by the strict greater-than operator in the CASE condition.

Visual Quiz - 3 Questions

Look at the execution_table, what is the CASE Result for department_id 20?

A 'Low'

B 'High'

C NULL

D 5000

💡 Hint

Check the row where department_id is 20 in the execution_table under CASE Result column.

At which step does the AVG(salary) first exceed 5000?

A Step 2

B Step 3

C Step 1

D Step 4

💡 Hint

Look at the AVG(salary) values in execution_table rows and find the first greater than 5000.

If the CASE condition changed to AVG(salary) >= 5000, what would be the CASE Result for department_id 30?

A 'Low'

B 'High'

C NULL

D Error

💡 Hint

Refer to variable_tracker for AVG(salary) for department_id 30 and consider the new condition.

Concept Snapshot

CASE with aggregate functions:
- Aggregate functions (e.g., AVG, SUM) compute values per group.
- CASE evaluates conditions on these aggregate results.
- Syntax: CASE WHEN aggregate_condition THEN result ELSE other_result END
- Use GROUP BY to group rows before aggregation.
- Output depends on aggregate values per group.

Full Transcript

This visual execution shows how a SQL query uses CASE with aggregate functions. The query groups employees by department_id and calculates the average salary per group. Then, the CASE statement checks if the average salary is greater than 5000. If true, it outputs 'High'; otherwise, 'Low'. The execution table traces each department's average salary, the CASE condition evaluation, and the final output row. The variable tracker shows how department_id, average salary, and CASE results change step-by-step. Key moments clarify why CASE uses aggregate results and what happens at boundary values. The quiz tests understanding of CASE results and condition changes. This helps beginners see how CASE works with aggregates in SQL.

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