MySQL GROUP BY

Last updated: October 19, 2026Oct 19, 2026 5 min read

GROUP BY puts rows that share a value into groups, then aggregate functions such as COUNT, SUM and AVG calculate one result per group. It turns detailed data into summaries: sales per region, orders per month, students per class.

1. How GROUP BY Works

Table: sales (9 rows)

2. Syntax

SELECT group_columns, AGG(column) ...
FROM table
[WHERE row_condition]
GROUP BY group_columns
[HAVING group_condition]
[ORDER BY ...];

3. Grouping by One Column

SELECT region, COUNT(*) AS sales, SUM(amount) AS revenue, ROUND(AVG(amount)) AS avg_sale
FROM sales
GROUP BY region
ORDER BY revenue DESC;

Result: 3 rows

4. Grouping by Several Columns

Each unique combination becomes a group:

SELECT region, product, SUM(qty) AS units, SUM(amount) AS revenue
FROM sales
GROUP BY region, product
ORDER BY region, revenue DESC;

Result: 7 rows

5. Filtering Rows First with WHERE

WHERE removes rows before grouping. Revenue per rep, counting only kettles:

SELECT rep, COUNT(*) AS kettles_sold, SUM(amount) AS kettle_revenue
FROM sales
WHERE product = 'Kettle'
GROUP BY rep;

Result: 4 rows

To filter groups after aggregation, use HAVING.

6. Grouping by Expressions and Dates

Group by a calculated value such as the month:

SELECT DATE_FORMAT(sold_on, '%Y-%m') AS month, COUNT(*) AS sales, SUM(amount) AS revenue
FROM sales
GROUP BY month
ORDER BY month;

Result: 3 rows

Group into price bands with CASE:

SELECT CASE WHEN amount < 500 THEN 'small' WHEN amount < 1000 THEN 'medium' ELSE 'large' END AS size_band,
       COUNT(*) AS sales
FROM sales
GROUP BY size_band
ORDER BY MIN(amount);

Result: 3 rows

7. The ONLY_FULL_GROUP_BY Rule

Every column in the SELECT list must either be in GROUP BY or be inside an aggregate. Otherwise MySQL can't know which row's value to show, and (with the default ONLY_FULL_GROUP_BY mode) it refuses:

SELECT region, rep, SUM(amount) FROM sales GROUP BY region;

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

Fix it by grouping by rep as well, aggregating it, or using ANY_VALUE() when any value will do:

SELECT region, GROUP_CONCAT(DISTINCT rep ORDER BY rep) AS reps, SUM(amount) AS revenue
FROM sales
GROUP BY region;

Result: 3 rows

8. Subtotals with WITH ROLLUP

WITH ROLLUP adds subtotal rows and a grand total (shown with NULL in the grouped columns). GROUPING() tells you which rows are totals:

SELECT IF(GROUPING(region), 'ALL REGIONS', region) AS region,
       IF(GROUPING(product), 'all products', product) AS product,
       SUM(amount) AS revenue
FROM sales
GROUP BY region, product WITH ROLLUP;

Result: 11 rows

9. COUNT(DISTINCT) per Group

SELECT region, COUNT(*) AS sales, COUNT(DISTINCT product) AS products, COUNT(DISTINCT rep) AS reps
FROM sales
GROUP BY region;

Result: 3 rows

10. Sorting Groups

MySQL 8 doesn't sort GROUP BY results implicitly. Add ORDER BY, which may use aliases and aggregates:

SELECT rep, SUM(amount) AS revenue FROM sales GROUP BY rep ORDER BY revenue DESC LIMIT 3;

Result: 3 rows

11. Practice Exercises

SELECT product, SUM(qty) AS units FROM sales GROUP BY product ORDER BY units DESC;

SELECT rep, MIN(sold_on) AS first_sale, MAX(sold_on) AS latest_sale FROM sales GROUP BY rep ORDER BY rep;

SELECT MONTH(sold_on) AS m, SUM(amount) AS revenue FROM sales WHERE region = 'South' GROUP BY m ORDER BY m;

12. Summary

• GROUP BY makes one result row per distinct value (or combination of values).
• Aggregates (COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT) summarise each group.
• WHERE filters rows before grouping; HAVING filters groups after.
• Selected columns must be grouped or aggregated (ONLY_FULL_GROUP_BY).
• WITH ROLLUP adds subtotals; add ORDER BY to sort the groups.