MySQL GROUP BY: Summarise Rows per Category with Examples

GROUP BY splits the rows into groups and gives you one result row per group. You usually combine it with an aggregate function (Aggregate Functions).

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    added_on DATE NOT NULL
);

INSERT INTO products (name, category, price, stock, added_on) VALUES
    ('Notebook',      'stationery',  80.00, 120, '2026-01-10'),
    ('Gel Pen',       'stationery',  15.50, 300, '2026-01-12'),
    ('Backpack',      'bags',       999.00,  25, '2026-02-01'),
    ('Marker Set',    'stationery', 145.00,   0, '2026-02-15'),
    ('Desk Lamp',     'home',       650.00,  40, '2026-03-03'),
    ('Water Bottle',  'home',       220.00,  75, '2026-03-20'),
    ('Laptop Sleeve', 'bags',       450.00,  18, '2026-04-02'),
    ('Sticky Notes',  'stationery',  35.00, 210, '2026-04-18');

query.sql

SQL

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

Output

OUTPUT

+------------+----------+-----------+-------+
| category   | products | avg_price | units |
+------------+----------+-----------+-------+
| bags       |        2 |    724.50 |    43 |
| home       |        2 |    435.00 |   115 |
| stationery |        4 |     68.88 |   630 |
+------------+----------+-----------+-------+
3 rows in set

All rows with the same category form a group. For each group, MySQL calculated the count, the average price and the total stock. Three groups, so three rows.

The rule

Every column in the SELECT list must be either:

in the GROUP BY, or
inside an aggregate function, or
functionally dependent on the grouped columns (for example, a column that is determined by the primary key you group by).

That is the ONLY_FULL_GROUP_BY rule, which is part of the default sql_mode (ONLY_FULL_GROUP_BY).

Several columns: one group for each combination

query.sql

SQL

SELECT category, stock > 0 AS in_stock, COUNT(*) AS products
FROM products
GROUP BY category, stock > 0
ORDER BY category, in_stock;

Output

OUTPUT

+------------+----------+----------+
| category   | in_stock | products |
+------------+----------+----------+
| bags       |        1 |        2 |
| home       |        1 |        2 |
| stationery |        0 |        1 |
| stationery |        1 |        3 |
+------------+----------+----------+
4 rows in set

Stationery appears twice: one group for items in stock, another for the out-of-stock Marker Set.

WHERE first, then GROUP BY

WHERE removes rows before grouping:

query.sql

SQL

SELECT category, COUNT(*) AS cheap_products
FROM products
WHERE price < 500
GROUP BY category
ORDER BY cheap_products DESC, category;

Output

OUTPUT

+------------+----------------+
| category   | cheap_products |
+------------+----------------+
| stationery |              4 |
| bags       |              1 |
| home       |              1 |
+------------+----------------+
3 rows in set

Bags are listed with one product (Laptop Sleeve at 450), because the Backpack (999) was removed by WHERE. Filtering groups by their results (for example, “categories with at least two products”) needs HAVING (HAVING).

GROUP BY does not sort in MySQL 8

In MySQL 5.7 and older, GROUP BY also sorted the result. In MySQL 8.0 and later it does not. The groups come out in whatever order is cheapest for the server. The old GROUP BY category DESC syntax was removed, too:

query.sql

SQL

SELECT category, COUNT(*) AS products FROM products GROUP BY category DESC;

Output

OUTPUT

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC'

If you want sorted results, always write ORDER BY. Do not depend on the group order. Code that worked on 5.7 without ORDER BY may return groups in a different order after an upgrade.

Group by the key, not by a name

If two different customers are both called “Asha Rao”, grouping by name merges them. Group by the id and show the name next to it:

query.sql

SQL

SELECT id, name, COUNT(*) AS rows_in_group FROM (SELECT 1 AS id, 'Asha' AS name UNION ALL SELECT 2, 'Asha') AS t GROUP BY id, name ORDER BY id;

Output

OUTPUT

+----+------+---------------+
| id | name | rows_in_group |
+----+------+---------------+
|  1 | Asha |             1 |
|  2 | Asha |             1 |
+----+------+---------------+
2 rows in set

NULL is a group too

All NULL values in a grouping column form one group:

query.sql

SQL

1
2
3

CREATE TABLE tickets (id INT AUTO_INCREMENT PRIMARY KEY, assignee VARCHAR(10) NULL);
INSERT INTO tickets (assignee) VALUES ('Asha'), (NULL), ('Asha'), (NULL), ('Ben');
SELECT assignee, COUNT(*) AS tickets FROM tickets GROUP BY assignee ORDER BY assignee;

Output

OUTPUT

+----------+---------+
| assignee | tickets |
+----------+---------+
| NULL     |       2 |
| Asha     |       2 |
| Ben      |       1 |
+----------+---------+
3 rows in set

The NULL group (unassigned) is listed first when sorted ascending.

GROUP BY and DISTINCT

SELECT DISTINCT category FROM products and SELECT category FROM products GROUP BY category give the same list. Use GROUP BY when you also need an aggregate.

Order of evaluation

FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY (Order of Evaluation). That is why the SELECT list can only show grouped columns and aggregates.

lightbulb

Think "one row per …"

Before you write a GROUP BY query, finish the sentence: “I want one row per ___.” Whatever fills the blank goes in the GROUP BY.