GROUP BY: MySQL | Himanshu Sharma

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonSummarise rows per category, customer, month or any other grouping, and explain why every selected column must be grouped or aggregated.

One row per group

Aggregate functions on their own give one answer for the whole table. Usually you want one answer for each group: orders per customer, revenue per month, products per category. GROUP BY does that.

sql

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

result

+-------------+----------+-----------+
| category_id | products | avg_price |
+-------------+----------+-----------+
|           1 |        4 |     88.37 |
|           2 |        3 |     36.48 |
|           3 |        3 |     35.25 |
|           4 |        3 |     18.16 |
|           5 |        2 |     34.99 |
+-------------+----------+-----------+

MySQL sorts the rows into groups with the same category_id, then calculates COUNT(*) and AVG(price) once per group. Five categories give five rows.

Think of it in three steps: split the rows into groups, apply the aggregates to each group, combine the answers into the result.

The rule for the select list

Every column in the select list must be either in the GROUP BY or inside an aggregate function. Within a group there is one category_id but many different name values, so selecting name would be ambiguous:

sql

SELECT category_id, name, COUNT(*) FROM products GROUP BY category_id;

result

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shop.products.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

This rule is enforced by the ONLY_FULL_GROUP_BY SQL mode, which is on by default. Older MySQL versions allowed such queries and returned an arbitrary name, which led to wrong reports nobody noticed. If you really want one name per group, aggregate it: MIN(name) or GROUP_CONCAT(name).

An exception applies when columns are functionally dependent on a grouped primary key, for example grouping by customers.id and selecting customers.first_name. MySQL 5.7.5 and later recognise that.

Filtering before grouping with WHERE

WHERE removes rows before they are grouped. Count only the orders that were not cancelled, per customer:

sql

SELECT customer_id, COUNT(*) AS orders FROM orders WHERE status <> 'cancelled' GROUP BY customer_id ORDER BY orders DESC, customer_id;

result

+-------------+--------+
| customer_id | orders |
+-------------+--------+
|           1 |      4 |
|           6 |      3 |
|           2 |      2 |
|           3 |      2 |
|           4 |      2 |
|          10 |      2 |
|           7 |      1 |
|           8 |      1 |
|          11 |      1 |
+-------------+--------+

Customers 9 and 12 do not appear at all, because they have no order rows to form a group. To list them with a count of 0 you need an outer join, coming up soon.

Totals per group

Revenue per order is the sum of its lines:

sql

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

result

+----------+-------------+------------+
| order_id | order_total | item_lines |
+----------+-------------+------------+
|       12 |      249.97 |          2 |
|       20 |      218.74 |          2 |
|        4 |      199.99 |          1 |
|       10 |      128.50 |          2 |
|       11 |      109.45 |          3 |
+----------+-------------+------------+

And units sold per product:

sql

SELECT product_id, SUM(quantity) AS units FROM order_items GROUP BY product_id ORDER BY units DESC, product_id LIMIT 5;

result

+------------+-------+
| product_id | units |
+------------+-------+
|          9 |     7 |
|          5 |     6 |
|         13 |     6 |
|          1 |     5 |
|         12 |     5 |
+------------+-------+

These show product ids, not names, because the names are in another table. Joins fix that in the next section.

Grouping by several columns

List more than one column and each combination becomes a group:

sql

SELECT country, city, COUNT(*) AS customers FROM customers WHERE country = 'India' GROUP BY country, city;

result

+---------+-----------+-----------+
| country | city      | customers |
+---------+-----------+-----------+
| India   | Delhi     |         1 |
| India   | Mumbai    |         1 |
| India   | Bengaluru |         1 |
+---------+-----------+-----------+

sql

SELECT customer_id, status, COUNT(*) AS orders FROM orders WHERE customer_id IN (1, 6) GROUP BY customer_id, status ORDER BY customer_id, status;

result

+-------------+-----------+--------+
| customer_id | status    | orders |
+-------------+-----------+--------+
|           1 | shipped   |      1 |
|           1 | delivered |      3 |
|           6 | pending   |      1 |
|           6 | shipped   |      1 |
|           6 | delivered |      1 |
+-------------+-----------+--------+

Grouping by an expression

You can group by a calculated value, such as the month of a date. Include the year, or January 2024 and January 2025 would merge:

sql

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

result

+---------+--------+
| month   | orders |
+---------+--------+
| 2024-01 |      2 |
| 2024-02 |      3 |
| 2024-03 |      4 |
| 2024-04 |      3 |
| 2024-05 |      3 |
| 2024-06 |      3 |
| 2024-07 |      2 |
+---------+--------+

MySQL also lets you group by the alias or by a position number (GROUP BY 1), but writing the expression is clearer and portable.

Lists inside groups

GROUP_CONCAT turns the members of a group into text:

sql

SELECT status, COUNT(*) AS orders, GROUP_CONCAT(id ORDER BY id) AS order_ids FROM orders GROUP BY status ORDER BY status;

result

+-----------+--------+-----------------------------+
| status    | orders | order_ids                   |
+-----------+--------+-----------------------------+
| pending   |      3 | 9,14,19                     |
| shipped   |      4 | 4,8,12,20                   |
| delivered |     11 | 1,2,3,5,7,10,11,13,15,16,18 |
| cancelled |      2 | 6,17                        |
+-----------+--------+-----------------------------+

Order of work

Here is the logical order MySQL follows, which explains many "why can't I..." questions:

FROM finds the rows.
WHERE filters rows.
GROUP BY builds groups.
Aggregates are calculated, HAVING filters groups (next lesson).
SELECT computes the output columns.
ORDER BY sorts, and LIMIT cuts.

Common mistakes

Selecting a column that is not grouped or aggregated.
Using WHERE to filter on an aggregate. Use HAVING.
Grouping by the wrong level, such as customer_id when you need customer_id and status.
Grouping by month without the year.
Forgetting groups with no rows do not appear. A customer with no orders is missing from a grouped count.

Exercise

Write queries to show: (1) the number of employees and total salary per department id, (2) the number of customers per country sorted with the biggest first, and (3) for each order status the earliest and latest order date.

Show one possible answer

sql

SELECT department_id, COUNT(*) AS staff, SUM(salary) AS salary_total FROM employees GROUP BY department_id;
SELECT country, COUNT(*) AS customers FROM customers GROUP BY country ORDER BY customers DESC, country;
SELECT status, MIN(order_date) AS first_order, MAX(order_date) AS last_order FROM orders GROUP BY status;

result

+---------------+-------+--------------+
| department_id | staff | salary_total |
+---------------+-------+--------------+
|             1 |     6 |    582000.00 |
|             2 |     3 |    240000.00 |
|             3 |     1 |     72000.00 |
|          NULL |     1 |     90000.00 |
+---------------+-------+--------------+
+-------------+-----------+
| country     | customers |
+-------------+-----------+
| India       |         3 |
| France      |         1 |
| Ireland     |         1 |
| Italy       |         1 |
| Japan       |         1 |
| Nigeria     |         1 |
| Portugal    |         1 |
| South Korea |         1 |
| UK          |         1 |
| USA         |         1 |
+-------------+-----------+
+-----------+-------------+------------+
| status    | first_order | last_order |
+-----------+-------------+------------+
| delivered | 2024-01-05  | 2024-06-25 |
| shipped   | 2024-02-02  | 2024-07-19 |
| cancelled | 2024-03-01  | 2024-06-10 |
| pending   | 2024-03-30  | 2024-07-07 |
+-----------+-------------+------------+

Victor Lang, who has no department, forms his own group with department_id NULL.

Key points

GROUP BY splits rows into groups and returns one row per group, so aggregates are calculated per group.
Every selected column must either appear in GROUP BY or be inside an aggregate function (ONLY_FULL_GROUP_BY).
WHERE filters rows before they are grouped. Use it to limit what goes into the groups.
You can group by several columns or by expressions, such as the month of a date.

One row per group

The rule for the select list

Filtering before grouping with WHERE

Totals per group

Grouping by several columns

Grouping by an expression

Lists inside groups

Order of work

Common mistakes

Exercise

Key points

Check yourself