SQL CASE WHEN and Conditional Aggregation

Labelling rows

A CASE expression can go anywhere a value can: in SELECT, ORDER BY, GROUP BY or inside a function. The first WHEN that is true wins; if none is true, the ELSE value is used, or NULL when there is no ELSE.

Conditional aggregation

Inside an aggregate, CASE decides what each row contributes. SUM(CASE WHEN salary >= 64000 THEN 1 ELSE 0 END) adds 1 for each matching row and 0 for the rest, giving a count of matches per group, next to other totals in the same query.

Divide that count by COUNT(*) to get a rate. Multiply by 100.0 first: dividing two integers in PostgreSQL drops the decimals.

The example tables

Every example on this page uses these tables. They are small on purpose, so you can check each result by eye.

Examples

SELECT name, salary,
       CASE
         WHEN salary >= 80000 THEN 'High'
         WHEN salary >= 60000 THEN 'Mid'
         ELSE 'Entry'
       END AS band
FROM employees
ORDER BY salary DESC, name;

SELECT dept_id,
       COUNT(*) AS employees,
       SUM(CASE WHEN salary >= 64000 THEN 1 ELSE 0 END) AS earning_64k_plus,
       ROUND(100.0 * SUM(CASE WHEN salary >= 64000 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id
ORDER BY dept_id;

SELECT customer_id,
       SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 1 THEN amount ELSE 0 END) AS jan,
       SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 2 THEN amount ELSE 0 END) AS feb,
       SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 3 THEN amount ELSE 0 END) AS mar
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Common mistakes

• Conditions in the wrong order. With WHEN salary >= 60000 first, a 95,000 salary is labelled "Mid" and never reaches the "High" branch.
• Forgetting ELSE 0. Without ELSE 0, non-matching rows give NULL. SUM skips them, but a group with no matches returns NULL instead of 0.
• Integer division. SUM(…) / COUNT(*) of two integers can round down to 0 in PostgreSQL. Write 100.0 * SUM(…) / COUNT(*).

Practise CASE WHEN

Interview questions that use this concept, easiest first. Each one has the step-by-step visualizer and answer checking.

1. 1

   Ad Click-Through Rate (CTR)

   Calculate the percentage CTR per ad campaign rounded to 2 decimals, safely handling campaigns with zero impressions.

   Easy✓ Solved
2. 2

   Listing Booking Windows

   Count each listing's bookings in the last 90 days, the last 365 days and all time.

   Easy✓ Solved
3. 3

   Valid Promotion Order Share

   Percentage of all orders that used a promotion that was actually valid on the order date.

   Easy✓ Solved
4. 4

   Composer Post Rate by App

   Yesterday's share of opened composers that ended in a published post, per app.

   Easy✓ Solved
5. 5

   Amazon: Repeat Purchase Rate

   Calculate the percentage of all registered customers who have placed two or more orders, rounded to 2 decimal places.

   Medium✓ Solved
6. 6

   Amazon: Monthly Category Revenue Pivot

   Pivot monthly product category sales into dedicated columns for Electronics, Apparel, and Home merchandise.

   Medium✓ Solved

Want a full set? Work through the GROUP BY & Aggregation study plan.

Questions people ask

Can I use COUNT instead of SUM?

Yes: COUNT(CASE WHEN cond THEN 1 END) counts matches, because rows without a match return NULL and COUNT skips NULLs.

Is there a shorter IF function?

MySQL has IF(cond, a, b) and PostgreSQL has FILTER (WHERE cond) for aggregates, but CASE WHEN works in both.

Related concepts

• SQL GROUP BY Explained with Examples
• SQL NULL Handling and COALESCE Explained