Filtering Groups with HAVING

You've learned that WHERE filters individual rows. But what if you want to filter groups after using GROUP BY? That's where HAVING comes in!

WHERE vs HAVING

Clause	Filters...	When it runs
<code>WHERE</code>	Individual rows	Before grouping
<code>HAVING</code>	Groups	After grouping

Example: Categories with Multiple Products

-- This DOESN'T work:
SELECT category, COUNT(*) FROM products
WHERE COUNT(*) > 1   -- ❌ ERROR! Can't use aggregate in WHERE
GROUP BY category;
-- This DOES work:
SELECT category, COUNT(*) FROM products
GROUP BY category
HAVING COUNT(*) > 1;  -- ✅ Filters after grouping

How It Works (Step by Step)

• FROM products — start with all rows
• GROUP BY category — create groups (Electronics: 2, Office: 2, Books: 1)
• HAVING COUNT(*) > 1 — remove groups with 1 or fewer items
• Result: only Electronics (2) and Office (2)

💡 When to Use HAVING

• "Show only categories with more than 5 products"
• "Find departments where average salary exceeds $50,000"
• "List months where total sales are above $10,000"

⚠️ Common Mistake

Don't use WHERE for aggregate conditions — always use HAVING.

Your Task

Find categories that have more than 1 product. Show the category and the count.