Grouping with GROUP BY

GROUP BY splits rows into groups and calculates aggregates for each group — "average price per category", "orders per customer".

Example

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

Expected output

+-------------+----------+-----------+
|  category   | products | avg_price |
+-------------+----------+-----------+
| Accessories |        3 |      55.5 |
| Audio       |        1 |     199.0 |
| Displays    |        1 |    249.99 |
| Video       |        1 |      59.9 |
+-------------+----------+-----------+

The database gathers rows with the same category into a group, then computes COUNT and AVG for each group — one result row per category.

Real-world analogy: sorting a pile of receipts into envelopes by shop, then writing the total on each envelope.

The rule

Every column in the SELECT must either be in the GROUP BY or be inside an aggregate function. Otherwise the database can't know which value to show for the group.

Grouping by several columns

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

+-------------+-----------+--------+
| customer_id |  status   | orders |
+-------------+-----------+--------+
|           1 | delivered |      2 |
|           1 | pending   |      1 |
|           2 | shipped   |      1 |
|           3 | cancelled |      1 |
|           3 | delivered |      1 |
|           4 | pending   |      1 |
|           5 | shipped   |      1 |
+-------------+-----------+--------+

One row per combination of customer and status.

Grouping by a calculation

SELECT order_id, SUM(quantity * unit_price) AS order_total
FROM order_items
GROUP BY order_id
ORDER BY order_total DESC;

+----------+-------------+
| order_id | order_total |
+----------+-------------+
|        4 |      583.98 |
|        2 |      249.99 |
|        7 |         241 |
|        3 |       234.5 |
|        8 |       179.7 |
|        1 |       160.0 |
|        6 |          89 |
|        5 |        59.9 |
+----------+-------------+

Common mistakes

• Selecting a column that isn't grouped or aggregated. Add it to GROUP BY or wrap it in an aggregate.
• Forgetting ORDER BY. Grouped results come out in no guaranteed order.

Practice

Count how many customers live in each city (including the unknown city), most customers first.

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

+----------+-----------+
|   city   | customers |
+----------+-----------+
| London   |         2 |
| NULL     |         1 |
| Boston   |         1 |
| Helsinki |         1 |
| New York |         1 |
+----------+-----------+