Aggregate functions summarize an entire table, but real questions often ask for separate statistics: products per category, orders per user, or units purchased per customer. SQL uses GROUP BY to place rows with the same value into a group, then applies aggregates to each group.
| id | name | price | category |
|---|---|---|---|
| 1 | Keyboard | 80 | Electronics |
| 2 | Mouse | 40 | Electronics |
| 3 | Coffee Mug | 15 | Home |
| 4 | Backpack | 60 | Fashion |
| 5 | Webcam | 55 | Electronics |
From one total to one result per group
SELECT COUNT(*) FROM products tells us only that five products exist. With GROUP BY category, rows sharing a category form a group first, then COUNT(*) runs for each: Electronics 3, Fashion 1, Home 1.
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;Calculate several values per group
GROUP BY is not limited to COUNT. Each category can have a count, average, highest, and lowest price in one query. A result row now represents a group rather than one original product.
Why must normal columns appear in GROUP BY?
category labels each group, so it can appear in SELECT. If you group only by category but also select name, the Electronics group contains Keyboard, Mouse, and Webcam. The database cannot choose one name for that summary row, so strict SQL modes reject the query.
SELECT category,
COUNT(*) AS product_count,
AVG(price) AS average_price,
MAX(price) AS highest_price,
MIN(price) AS lowest_price
FROM products
GROUP BY category;GROUP BY with JOIN
To count orders per user, LEFT JOIN users and orders, then group by user. LEFT JOIN keeps Diana even with no orders. Use COUNT(o.id) rather than COUNT(*): Diana still has a joined row, but o.id is NULL, so COUNT(o.id) ignores it and correctly returns zero orders.
SELECT u.name, COUNT(o.id) AS order_count
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
GROUP BY u.id, u.name;WHERE filters rows; HAVING filters groups
WHERE runs before grouping and filters original rows. HAVING runs after aggregation and filters group results. To find categories with at least two products, use HAVING COUNT(*) >= 2, not WHERE COUNT(*). Both can appear together: keep products priced at least 40, group by category, then keep groups that still have at least two products.
Break down a complete summary query
“Show users with at least two orders, sorted by order count descending” becomes: join users and orders; group by user; count orders; keep counts of at least two with HAVING; sort descending. As SQL grows, explain FROM, JOIN, WHERE, GROUP BY, HAVING, and ORDER BY one stage at a time.
See the “intermediate step” of GROUP BY
| category | count | average_price |
|---|---|---|
| Electronics | 3 | 58.33 |
| Home | 1 | 15.00 |
| Fashion | 1 | 60.00 |
| Stationery | 1 | 8.00 |
6 source rows are grouped by category before each group is summarized.KNOWLEDGE QUIZ
KNOWLEDGE QUIZ
Product summary by category
A merchandise manager needs product count and average price per category, sorted from highest to lowest average price. Return only category, product_count, and average_price.
High-value customer report
Find users who spent at least 100 on completed orders in 2026. Join users and orders; keep rows with status completed and order_date on or after 2026-01-01; group by user; return user_name, order_count, and total_spent; filter the total with HAVING and sort total_spent descending.