Aggregates, GROUP BY and HAVING
COUNT, SUM, AVG, MIN and MAX squash rows into one number. GROUP BY gives one row per group, and HAVING filters the groups after WHERE has filtered the rows.
Many rows, one number
An aggregate function reads a whole column and gives back one value:
COUNT(*)counts rows.COUNT(stock)counts rows wherestockis not NULL.SUM(x),AVG(x),MIN(x),MAX(x)do what they say.
SELECT COUNT(*), AVG(price) FROM products; returns one row about the whole shop.
Many rows become one
GROUP BY category sorts the rows into piles, one per category, then runs the aggregates on each pile. You get one row per category.
The rule: every column in SELECT must be in the GROUP BY or inside an aggregate. SELECT category, name, COUNT(*) ... GROUP BY category asks for one name to stand for a whole pile. SQLite quietly picks one, which looks like an answer and is not.
WHERE before, HAVING after
WHERE filters rows, before the piles are made. HAVING filters groups, after the aggregates are worked out, so it can use them:
SELECT category, COUNT(*) AS products
FROM products
WHERE price < 20
GROUP BY category
HAVING COUNT(*) >= 3;Read it as: keep the cheap products, pile them by category, keep the piles with at least three.
Run it: the shop by category
Run it, then change one thing and run it again.
Tables customers, products, orders, order_items
| id | name | year_group |
|---|---|---|
| 1 | Amara | 12 |
| 2 | Ben | 13 |
| 3 | Chloe | 12 |
| 4 | Dev | 12 |
| 5 | Ella | 13 |
| 6 | Femi | 13 |
| 7 | Grace | 10 |
| 8 | Hugo | 10 |
| id | name | category | price | stock |
|---|---|---|---|---|
| 1 | Sketchbook A4 | art | 4.5 | 30 |
| 2 | Acrylic paints | art | 8.0 | 12 |
| 3 | Brush set | art | 5.25 | 20 |
| 4 | Fineliners | art | 3.75 | 45 |
| 5 | Graph paper | maths | 2.0 | 60 |
| 6 | Scientific calculator | maths | 12.99 | 8 |
| 7 | Protractor | maths | 0.99 | 70 |
| 8 | Lab goggles | science | 6.5 | 15 |
| 9 | Periodic table poster | science | 3.0 | 25 |
| 10 | Hoodie | merch | 24.0 | 10 |
| 11 | Water bottle | merch | 9.5 | 18 |
| 12 | Lanyard | merch | 2.5 | 40 |
| id | customer_id | placed_on | status |
|---|---|---|---|
| 1 | 1 | 2026-09-16 | paid |
| 2 | 1 | 2026-09-03 | paid |
| 3 | 1 | 2026-09-21 | paid |
| 4 | 1 | 2026-09-03 | paid |
| 5 | 1 | 2026-09-20 | paid |
| 6 | 1 | 2026-09-27 | paid |
| 7 | 1 | 2026-09-16 | paid |
| 8 | 1 | 2026-09-09 | paid |
| 9 | 1 | 2026-09-08 | paid |
| 10 | 2 | 2026-09-28 | paid |
| 11 | 2 | 2026-09-30 | refunded |
| 12 | 2 | 2026-09-13 | paid |
First 12 of 41 rows. Query the table to see the rest.
| id | order_id | product_id | qty |
|---|---|---|---|
| 1 | 1 | 2 | 2 |
| 2 | 2 | 10 | 2 |
| 3 | 3 | 10 | 1 |
| 4 | 4 | 1 | 1 |
| 5 | 5 | 8 | 2 |
| 6 | 6 | 9 | 1 |
| 7 | 7 | 11 | 1 |
| 8 | 8 | 9 | 1 |
| 9 | 8 | 12 | 2 |
| 10 | 9 | 1 | 1 |
| 11 | 9 | 10 | 1 |
| 12 | 10 | 7 | 1 |
First 12 of 53 rows. Query the table to see the rest.
SELECT category,
COUNT(*) AS products,
ROUND(AVG(price), 2) AS avg_price,
MAX(price) AS dearest,
SUM(stock) AS in_stock
FROM products
GROUP BY category
ORDER BY products DESC, category;
Customers who keep coming back
Using orders, return each customer_id with their number of paid orders (status 'paid') in a column named paid_orders. Only keep customers with 6 or more paid orders.
SELECT customer_id, COUNT(*) AS paid_orders FROM orders;