[Solved] Identify the error in this SQL query using conditional aggregation:SELECT department, COUNT(CASE WHEN salary > 50000 THEN 1 ELSE 0 END) AS high_earners FROM employees GROUP BY department; | SQL

SQL - Advanced Query Patterns

Identify the error in this SQL query using conditional aggregation:

SELECT department, COUNT(CASE WHEN salary > 50000 THEN 1 ELSE 0 END) AS high_earners FROM employees GROUP BY department;

ASUM should be used instead of COUNT

BCOUNT counts zeros, so it counts all rows, not just high earners

CCASE syntax is invalid

DMissing GROUP BY clause

Step-by-Step Solution

Solution:

Step 1: Understand COUNT behavior with CASE
COUNT counts all non-NULL values. Here ELSE 0 returns zero, which is counted as a value, so all rows count.
Step 2: Identify correct fix
To count only high earners, ELSE should be NULL so those rows are ignored by COUNT.
Final Answer:
COUNT counts zeros, so it counts all rows, not just high earners -> Option B
Quick Check:
COUNT counts non-NULL, zero is counted [OK]

Quick Trick: Use ELSE NULL to exclude rows from COUNT [OK]

Common Mistakes:

Master "Advanced Query Patterns" in SQL

9 interactive learning modes - each teaches the same concept differently

More SQL Quizzes

Identify the error in this SQL query using conditional aggregation: