Code / SQL with Data / Unit 4: Summaries: aggregates and GROUP BY

HAVING: filter the groups HAVING: समूहों को छाँटना

Lesson 10 of 13 · about 14 minutes · WHERE filters rows, HAVING filters groups.

⚡ 0 XP

Step 1 of 2

WHERE vs HAVING

You cannot write WHERE COUNT(*) >= 3 — WHERE runs before the groups exist. To filter the groups themselves, use HAVING after GROUP BY:

SELECT category, COUNT(*) AS items FROM products GROUP BY category HAVING COUNT(*) >= 3;

The full order of a query:

SELECT … FROM …
WHERE — filter rows
GROUP BY — make groups
HAVING — filter groups
ORDER BY … LIMIT …

Example

SELECT category,
  MAX(price) AS top_price
FROM products
GROUP BY category
HAVING MAX(price) > 100;

🎯 Challenge: Categories with 3+ items +15 XP

Show category and COUNT(*) named items, but only for categories with at least 3 products.

Try twice to unlock the solution (0/2)

query.sqlCtrl ⏎

Tables (practice database)

Results

Press Run to see your query’s rows here.

Checklist

Press Run to check your code.

Your rows match the expected answer
Uses HAVING

Lesson notes (text version)

1. WHERE vs HAVING

You cannot write WHERE COUNT(*) >= 3 — WHERE runs before the groups exist. To filter the groups themselves, use HAVING after GROUP BY:

SELECT category, COUNT(*) AS items FROM products GROUP BY category HAVING COUNT(*) >= 3;

The full order of a query:

SELECT … FROM …
WHERE — filter rows
GROUP BY — make groups
HAVING — filter groups
ORDER BY … LIMIT …

                SELECT category,
  MAX(price) AS top_price
FROM products
GROUP BY category
HAVING MAX(price) > 100;

Challenge: Categories with 3+ items

Show category and COUNT(*) named items, but only for categories with at least 3 products.

2. WHERE and HAVING together

Often you need both. Who are the shop’s regular customers? Walk-in buyers have customer NULL, and all of them land in one NULL group — that is not a real customer. So:

WHERE customer IS NOT NULL removes walk-in rows first
GROUP BY customer adds up each customer
HAVING SUM(qty) >= 10 keeps the big buyers

                SELECT customer, COUNT(*) AS visits
FROM sales
GROUP BY customer;

Challenge: Our best customers

Show customer and SUM(qty) named units for named customers only (not NULL) who bought 10 or more units in total.