📎 This article includes 2 downloadable practice files ↓

📘 SQL Beginner Course · Lesson 5 of 12

Lesson 4 gave one total for the whole table. Reports need totals per something: per region, per product, per month. That’s GROUP BY, the SQL version of a pivot table.

One row per group

SELECT region, COUNT(*) AS customers
FROM customers
GROUP BY region
ORDER BY region;

The database sorts rows into groups by region and calculates COUNT for each.

The rule

Every column in SELECT must either be in GROUP BY or inside an aggregate (COUNT, SUM…). SELECT region, name, COUNT(*) … GROUP BY region doesn’t make sense: which name would it show for North?

More examples

SELECT status, COUNT(*) AS orders FROM orders GROUP BY status ORDER BY orders DESC;

SELECT category, COUNT(*) AS products, ROUND(AVG(price), 0) AS avg_price
FROM products GROUP BY category;

SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders
FROM orders GROUP BY month ORDER BY month;

strftime('%Y-%m', …) turns a date into “2026-07” in SQLite. (SQL Server: FORMAT(order_date,'yyyy-MM'); PostgreSQL: to_char(order_date,'YYYY-MM').)

Group by two columns

SELECT customer_id, status, COUNT(*) FROM orders GROUP BY customer_id, status;

HAVING: filter the groups

SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 28;

Clause order

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...;

Where beginners go wrong

Practice

Download shop.db and this lesson’s exercise file below. Open the database in the free DB Browser for SQLite (or sqlite3 shop.db), paste the exercises into the Execute SQL tab and write a query for each task. The expected result is printed under every task; answers are at the bottom of the file.