Aggregate analysis
- SQL course
- Intermediate
- Lesson 113/115
- 6 min
Analyse data with GROUP BY, HAVING and CASE.
Aggregate analysis
In this challenge you will analyze categories with GROUP BY, HAVING and CASE.
SELECT category,
SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) AS in_stock
FROM products
GROUP BY category
HAVING SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) > 2;This query categories with more than 2 in-stock products.
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. HAVING SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) > 2 runs after grouping and drops groups that fail the condition.
Practice Aggregate analysis
Count in-stock products per category with CASE and SUM, filter over 2 with HAVING.
Starter code
-- aggregate analysis
SELECT category,
SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) AS in_stock
FROM products
GROUP BY categorySample solution
SELECT category,
SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) AS in_stock
FROM products
GROUP BY category
HAVING SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) > 2;Expected output
category in_stock Elektronik 3
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Aggregate analysis: match the terms
| Term | Meaning |
|---|---|
GROUP BY |
group |
HAVING |
filter |
CASE |
condition |
SUM |
sum |
2 What filters groups?
- LIMIT
- WHERE
- DISTINCT
- HAVING Correct answer
Explanation: HAVING filters groups.