Lesson 8 • Intermediate Track
GROUP BY & HAVING
By the end of this lesson you'll be able to turn a flat table into a summary report — counting, summing, and averaging per group with GROUP BY, then filtering those groups with HAVING. This is the leap from "show me the rows" to "show me the totals", and it's the heart of every dashboard and analytics query.
What You'll Learn
- ✓Split a table into groups with GROUP BY
- ✓Run COUNT, SUM and AVG once per group
- ✓Group by one column — or several at once
- ✓Filter whole groups with HAVING
- ✓See why WHERE can't filter an aggregate
- ✓Order the clauses correctly: WHERE → GROUP BY → HAVING
COUNT, SUM, and AVG — this lesson runs them once per group instead of once for the whole table. The in-browser editor lets you write and edit SQL; to run it, copy your query into a free playground like sqliteonline.com or db-fiddle.com. Every example below shows the expected result so you can check yourself. Our Sample Table: products
Every query in this lesson runs against this little products table — the same one from the SELECT lesson. Notice the category column: Electronics appears three times, and Kitchen, Stationery, and Home appear once each. That's what makes the grouped results easy to check by hand.
Result:
| id | product_name | category | price | stock |
|---|---|---|---|---|
| 1 | Wireless Mouse | Electronics | 24.99 | 120 |
| 2 | Coffee Mug | Kitchen | 9.5 | 300 |
| 3 | Mechanical Keyboard | Electronics | 79 | 45 |
| 4 | Notebook | Stationery | 3.25 | 500 |
| 5 | Desk Lamp | Home | 32 | 80 |
| 6 | USB-C Cable | Electronics | 12.99 | 200 |
1. From One Total to One Per Group
In the last lesson, an aggregate like COUNT(*) squashed the entire table into a single number. GROUP BY changes that: it first splits the rows into groups (one per distinct value), then runs the aggregate once inside each group. You get one output row per group instead of one for the whole table.
🗂️ Real-world analogy
Picture a pile of receipts. Counting the whole pile gives you one number. GROUP BY store is sorting them into a folder per store first, then counting each folder. Same counting, but now you can compare stores.
No GROUP BY — one number
An aggregate over the whole table returns a single value.
-- WITHOUT GROUP BY, an aggregate collapses the WHOLE table to one number
SELECT COUNT(*) AS total_products
FROM products;
-- Our table has 6 rows, so this returns a single value: 6.
-- Useful, but it tells you nothing about each category.Result — 1 row:
| total_products |
|---|
| 6 |
2. GROUP BY with COUNT — One Aggregate Per Group
Add GROUP BY category and SQL builds one bucket per category, then counts the rows in each. Electronics holds ids 1, 3 and 6, so its count is 3; the other three categories have a single product each. The result has exactly one row per distinct category.
GROUP BY becomes the label of each output row, and the aggregate becomes the value. category + COUNT(*) reads as "for each category, how many rows?"GROUP BY category — COUNT(*)
Count the products inside each category.
-- WITH GROUP BY, the aggregate runs once PER group
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;
-- SQL splits the 6 rows into one bucket per category,
-- then counts the rows inside each bucket.
-- One output row per distinct category.Result — 4 rows — one per category:
| category | product_count |
|---|---|
| Electronics | 3 |
| Kitchen | 1 |
| Stationery | 1 |
| Home | 1 |
Your Turn: count per category
Fill in the two blanks to count how many products sit in each category. One blank is the aggregate, the other is the clause that makes the buckets. The expected result is in the comments so you can check yourself.
🎯 Your Turn: GROUP BY + COUNT
Replace the ___ blanks with COUNT(*) and GROUP BY.
-- 🎯 YOUR TURN — fill in the two blanks, then press "Try it Yourself"
-- Goal: count how many products are in each category.
SELECT category, ___ AS product_count -- 👉 the aggregate that counts rows: COUNT(*)
FROM products
___ category; -- 👉 the clause that makes the buckets: GROUP BY
-- ✅ Expected result (4 rows):
-- Electronics | 3 , Kitchen | 1 , Stationery | 1 , Home | 13. SUM and AVG Per Group
Any aggregate works with GROUP BY, not just COUNT. Swap in SUM(stock) to total the stock in each category, or AVG(price) to average the prices. For single-product categories the "average" and "sum" are just that one product's value — only Electronics actually combines several rows.
SUM(stock) per category
Total the stock column inside each group.
-- A different aggregate, same grouping: total stock per category
SELECT category, SUM(stock) AS total_stock
FROM products
GROUP BY category;
-- For each category bucket, add up the stock column.
-- Electronics = 120 + 45 + 200 = 365, the rest have one row each.Result — Electronics = 120 + 45 + 200:
| category | total_stock |
|---|---|
| Electronics | 365 |
| Kitchen | 300 |
| Stationery | 500 |
| Home | 80 |
AVG(price) per category
Average the price inside each group, rounded to 2 dp.
-- AVG gives the average price inside each group
SELECT category, ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category;
-- Electronics averages three prices: (24.99 + 79.00 + 12.99) / 3.
-- ROUND(..., 2) trims the result to 2 decimal places.Result — (24.99 + 79.00 + 12.99) / 3 = 38.99:
| category | avg_price |
|---|---|
| Electronics | 38.99 |
| Kitchen | 9.5 |
| Stationery | 3.25 |
| Home | 32 |
4. Grouping by More Than One Column
List several columns after GROUP BY and SQL makes a bucket for each unique combination. GROUP BY category, supplier would give one row per category-and-supplier pair. Our table only has the columns above, but the rule is the same: more grouping columns means smaller, more specific groups.
Rule of thumb: the number of output rows equals the number of distinct combinations of your GROUP BY columns.
5. HAVING — Filter the Groups
HAVING filters the groups after they've been aggregated. It looks like WHERE, but it can test aggregate values such as COUNT(*) > 1 or AVG(price) > 20. WHERE runs before grouping, so it has no idea what a group's count is yet — that's exactly the job HAVING exists to do.
| Clause | Filters | When it runs |
|---|---|---|
| WHERE | Individual rows | Before grouping |
| HAVING | Whole groups | After grouping & aggregation |
WHERE when you can — it removes rows before grouping, so there's less to aggregate. Only reach for HAVING when the test needs an aggregate, like HAVING COUNT(*) > 1.HAVING COUNT(*) > 1
Keep only groups with more than one product.
-- HAVING filters the GROUPS after they are counted/summed
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 1;
-- WHERE can't see COUNT(*) (it runs before grouping), so you must
-- use HAVING. Only Electronics has more than one product.Result — only Electronics has more than one product:
| category | product_count |
|---|---|
| Electronics | 3 |
Your Turn: keep the busy categories
One blank this time — add the keyword that filters groups so only categories with more than one product survive. Remember: it's not WHERE.
🎯 Your Turn: HAVING
Return only categories with more than one product.
-- 🎯 YOUR TURN — add ONE line to keep only the busy categories.
-- Goal: show categories that have more than one product.
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
___ COUNT(*) > 1; -- 👉 the keyword that filters GROUPS (not WHERE!)
-- ✅ Expected result (1 row): Electronics | 36. The Logical Clause Order
When a query has all of these, the database runs them in a fixed logical order. You write them in this order too, or you'll get a syntax error:
SELECT columns / aggregates FROM table WHERE row_conditions -- 1. filter ROWS (before grouping) GROUP BY columns -- 2. build the groups HAVING group_conditions -- 3. filter GROUPS (after aggregating) ORDER BY columns -- 4. sort the result LIMIT n; -- 5. cap the rows
The query below uses three of these steps together. Walk the comments line by line to see which rows survive each stage.
WHERE + GROUP BY + HAVING together
Filter rows, group them, then filter the groups.
-- WHERE and HAVING work TOGETHER, in this order:
-- WHERE filters individual ROWS (before grouping)
-- GROUP BY makes the buckets
-- HAVING filters whole GROUPS (after aggregating)
SELECT category, COUNT(*) AS in_stock_lines, SUM(stock) AS total_stock
FROM products
WHERE stock >= 100 -- 1. keep only well-stocked rows (drops id 3 & 5)
GROUP BY category -- 2. bucket the survivors by category
HAVING SUM(stock) > 300; -- 3. keep buckets whose stock total beats 3
...Result — Kitchen's 300 does not beat 300, so it's dropped by HAVING:
| category | in_stock_lines | total_stock |
|---|---|---|
| Electronics | 2 | 320 |
| Stationery | 1 | 500 |
Common Errors (and the fix)
- Selecting a column that isn't grouped or aggregated:
SELECT category, product_name FROM products GROUP BY category;fails (or returns a random name) — which of the three Electronics names should it show? Every selected column must appear inGROUP BYor sit inside an aggregate likeCOUNT()/MAX(). - Using
WHEREto filter an aggregate:WHERE COUNT(*) > 1errors with "misuse of aggregate" —WHEREruns before grouping. Move that test toHAVING COUNT(*) > 1. HAVINGwithoutGROUP BY: it's only meaningful once groups exist. Add the matchingGROUP BY category, or if you truly want to filter rows, useWHEREinstead.- Clauses in the wrong order:
HAVINGbeforeGROUP BY, orWHEREafterGROUP BY, is a syntax error. The order is alwaysWHERE → GROUP BY → HAVING → ORDER BY.
📘 Quick Reference
| Pattern | Purpose |
|---|---|
| GROUP BY col | One group (and one aggregate result) per value of col |
| GROUP BY a, b | One group per unique combination of a and b |
| COUNT(*) per group | How many rows in each group |
| SUM(x) / AVG(x) per group | Total / average of x inside each group |
| HAVING COUNT(*) > n | Keep only groups with more than n rows |
| WHERE filters rows | Runs BEFORE grouping (can't see aggregates) |
| HAVING filters groups | Runs AFTER grouping (can test aggregates) |
Frequently Asked Questions
Q: What's the real difference between WHERE and HAVING?
WHERE filters rows before grouping, so it can't reference an aggregate. HAVING filters whole groups after aggregation, so it can test things like COUNT(*) or AVG(price). They often appear in the same query.
Q: Why do I get an error when I SELECT a column that isn't in GROUP BY?
Because the group might contain many different values for that column (three product names for Electronics, say), and SQL can't pick one. Put the column in GROUP BY, or wrap it in an aggregate.
Q: Can I use a column alias inside HAVING?
It varies by database. To stay portable, repeat the aggregate — write HAVING COUNT(*) > 1 rather than HAVING product_count > 1.
Q: Does GROUP BY sort the results?
Not guaranteed. Some databases happen to return groups in order, but if you need a specific order, add an explicit ORDER BY after the HAVING clause.
Mini-Challenge: Premium Categories
Put it all together — a brief, a blank canvas, and the expected result in the comments. Write it, then copy it into a playground to confirm. You'll need GROUP BY, AVG, and HAVING in one query.
🎯 Mini-Challenge
Average price per category, keeping only categories that average above 20.
-- 🎯 MINI-CHALLENGE
-- Using ONLY what this lesson covered (GROUP BY, AVG, HAVING):
-- 1. Show category and a column avg_price = ROUND(AVG(price), 2)
-- 2. Group by category
-- 3. Keep only the categories whose average price is above 20
--
-- ✅ Expected result (2 rows):
-- Electronics | 38.99 , Home | 32.00
-- (Kitchen 9.50 and Stationery 3.25 are filtered out by HAVING)
-- your query here🎉 Lesson Complete
- ✅
GROUP BYsplits the table into groups and runs each aggregate once per group - ✅
COUNT,SUM, andAVGall work per group — one output row per group - ✅ Listing several columns in
GROUP BYgroups by each unique combination - ✅
HAVINGfilters groups on an aggregate;WHEREfilters rows before grouping - ✅ The order is always
WHERE → GROUP BY → HAVING → ORDER BY - ✅ Next:
Subqueries— nesting one query inside another for even more powerful results
Sign up for free to track which lessons you've completed and get learning reminders.