📎 This article includes 2 downloadable practice files ↓
If you can build a pivot table, you already understand GROUP BY. It is the SQL way to say “one row per region, with totals”.
In this article
The pivot-table equivalent
SELECT region, SUM(amount) AS total_sales, COUNT(*) AS orders
FROM sales
GROUP BY region
ORDER BY total_sales DESC;Rows area = GROUP BY region; Values area = SUM(amount), COUNT(*).
Group by more than one column
SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY region, product;WHERE vs HAVING
- WHERE filters rows before grouping (like a report filter).
- HAVING filters groups after grouping (like filtering the pivot’s totals).
SELECT customer, SUM(amount) AS total
FROM sales
WHERE order_date >= '2018-01-01' -- only this year's rows
GROUP BY customer
HAVING SUM(amount) > 100000; -- only big customers⚠️ Every column in SELECT must be either in GROUP BY or inside an aggregate (SUM, COUNT, AVG, MIN, MAX). Most databases reject anything else.
Useful aggregates
| Function | Gives |
|---|---|
COUNT(*) |
Rows in the group |
COUNT(DISTINCT customer) |
Unique customers |
AVG(amount) |
Average |
MIN / MAX |
First/last date, smallest/largest value |
Practise these queries in the browser in the SQL Playground.
📎 Practice files for this article
- 📄SQLite practice databaseorders (400 rows) and monthly_sales tables for every query in the post.⬇ DB · 28 KB
- 🗃️GROUP BY / HAVING queries (.sql)The post's queries plus the classic 'not in GROUP BY' mistake.⬇ SQL · 516 B
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong