Aggregate analysis

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.

Exercise Aggregate analysis

Count in-stock products per category with CASE and SUM, filter over 2 with HAVING.

Show example 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;

Test yourself

Aggregate analysis: match the terms

GROUP BY

group
HAVING

filter
CASE

condition
SUM

sum
What filters groups?
- LIMIT
- WHERE
- DISTINCT
- HAVING
Show answer

HAVING — HAVING filters groups.

Next lesson: Subquery challenge

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies