Filter Groups with HAVING
Part of the SQL Foundations section of Coddy's PostgreSQL journey. Lesson 14 of 22.
WHERE filters individual rows before aggregation. HAVING filters completed groups using their summaries. This distinction matters when a report needs both a row condition and a group threshold.
Input table products:
id | name | category | price | stock | active | note |
|---|---|---|---|---|---|---|
1 | Map | travel | 12 | 5 | True | NULL |
2 | Notebook | books | 8 | 0 | True | Recycled |
3 | Atlas | books | 24 | 3 | False | NULL |
4 | Mug | home | 12 | 8 | True | Blue |
5 | lamp | home | 30 | 2 | False | Clearance |
6 | Mini map | travel | 6 | 10 | True | NULL |
SELECT category, SUM(stock) AS units FROM products GROUP BY category HAVING SUM(stock) >= 10 ORDER BY category;Home and travel pass because each category has at least ten units. A WHERE stock >= 10 condition would instead discard individual products before computing category totals.
Put row conditions in WHERE and aggregate conditions in HAVING.
Challenge
MediumReturn category and inventory_value for categories whose total price times stock is at least 100. Order by category.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
-- Write your SQL here.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in SQL Foundations
3Summarize Rows
Count Rows and ValuesTotals and AveragesGroup by CategoryFilter Groups with HAVINGCategory Restock BudgetPractice on your own: PostgreSQL playground