CASE with aggregates
- SQL course
- Intermediate
- Lesson 69/115
- 4 min
Use CASE inside an aggregate.
CASE with aggregates
You can use CASE inside an aggregate for conditional counting. SUM(CASE WHEN ... THEN 1 ELSE 0 END).
SELECT category,
SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) AS in_stock
FROM products
GROUP BY category;This query gives the count of in-stock products per category.
category, SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) AS … is selected from products. Rows are grouped by category; aggregate functions are computed per group.
CASE with aggregates — details
You produce 1/0 with CASE and sum them. This pattern is common for conditional counting.
For example, SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) gives the number of in-stock products per category in a single query.
Practice CASE with aggregates
Count in-stock products per category with SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END).
Starter code
-- conditional count
SELECT category,Sample solution
SELECT category,
SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) AS in_stock
FROM products
GROUP BY category;Expected output
category in_stock Kitap 5 Elektronik 3
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Add SUM
SELECT category,
____(CASE WHEN stock > 0 THEN 1 ELSE 0 END) AS in_stock
FROM products
GROUP BY category;SELECT category,
SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) AS in_stock
FROM products
GROUP BY category;Explanation: CASE gives 1 for items in stock; SUM adds them up to count in-stock products per category.
2 CASE with aggregates: match the terms
| Term | Meaning |
|---|---|
SUM(CASE) |
conditional count |
WHEN |
condition |
THEN 1 |
count |
ELSE 0 |
skip |
3 What does conditional counting use?
- MIN + HAVING
- COUNT + WHERE
- AVG + GROUP BY
- SUM + CASE Correct answer
Explanation: SUM and CASE do conditional counting.