SQL Tutorial › Functions & Grouping › GROUP BY

SQL GROUP BY

Q: Can I GROUP BY multiple columns?

Yes. GROUP BY dept_id, city creates one group for each distinct combination of the two columns.

Q: What is the difference between GROUP BY and ORDER BY?

GROUP BY combines rows into groups for aggregation. ORDER BY only sorts the final result. They are often used together.

IntermediateLesson 24 of 6912 min read9 queries run on MySQL 8.4

GROUP BY splits rows into groups that share a value and runs an aggregate on each group: headcount per department, revenue per month, orders per customer. It is the single most important clause for reporting, and this lesson shows exactly how MySQL builds the groups.

In this lesson

Group rows and aggregate each group
Group by several columns and by expressions
Combine GROUP BY with joins and ORDER BY
Add subtotals with WITH ROLLUP

GROUP BY syntax

MySQL

SELECT group_column, AGGREGATE(column)FROM table_name[WHERE row_condition]GROUP BY group_column[HAVING group_condition][ORDER BY ...];

Sample data

employees · 12 rows (some columns)

emp_id	first_name	dept_id	job_title	salary	city
100	Meera	NULL	CEO	250000	Mumbai
101	Asha	1	Engineering Manager	125000	Bengaluru
102	Rahul	1	Senior Developer	98000	Bengaluru
103	Priya	1	QA Engineer	72000	Pune
104	Karan	1	Developer	72000	Bengaluru
105	Vikram	2	Sales Manager	105000	Mumbai
106	Neha	2	Sales Executive	54000	Mumbai
107	Arjun	2	Sales Executive	48000	Kolkata
108	Kavya	3	Marketing Lead	88000	Pune
109	Rohan	3	Content Writer	45000	Pune
110	Sneha	4	HR Manager	78000	Delhi
111	Amit	1	Intern	25000	Bengaluru

Group by one column

Headcount and payroll per department:

SQL

SELECT dept_id,       COUNT(*)    AS staff,       SUM(salary) AS payrollFROM employeesGROUP BY dept_idORDER BY dept_id;

ResultMySQL 8.4

dept_id	staff	payroll
NULL	1	250000
1	5	392000
2	3	207000
3	2	133000
4	1	78000

5 rows in set

How this query works, step by step

FROM employees reads all 12 rows.
GROUP BY dept_id sorts the rows into piles with the same dept_id: NULL (the CEO), 1, 2, 3 and 4.
COUNT(*) and SUM(salary) run once per pile.
The result has one row per group: 5 rows.

Picture the piles for departments 2 and 3:

dept_id	rows in the group	COUNT(*)	SUM(salary)
2	Vikram 105000, Neha 54000, Arjun 48000	3	207000
3	Kavya 88000, Rohan 45000	2	133000

Show names instead of ids

Join the departments table and group by the name:

SQL

SELECT d.dept_name,       COUNT(*)           AS staff,       ROUND(AVG(e.salary)) AS avg_salaryFROM employees eJOIN departments d ON d.dept_id = e.dept_idGROUP BY d.dept_nameORDER BY staff DESC;

ResultMySQL 8.4

dept_name	staff	avg_salary
Engineering	5	78400
Sales	3	69000
Marketing	2	66500
HR	1	78000

4 rows in set

How this query works, step by step

The inner join drops the CEO (no department) and adds dept_name to each employee row.
GROUP BY d.dept_name makes one group per department name.
ORDER BY staff DESC lists the largest department first.

Group by several columns

One group for every combination of department and city:

SQL

SELECT dept_id, city, COUNT(*) AS staffFROM employeesWHERE dept_id IS NOT NULLGROUP BY dept_id, cityORDER BY dept_id, city;

ResultMySQL 8.4

dept_id	city	staff
1	Bengaluru	4
1	Pune	1
2	Kolkata	1
2	Mumbai	2
3	Pune	2
4	Delhi	1

6 rows in set

How this query works, step by step

WHERE removes the CEO before grouping.
Rows with the same dept_id and the same city form one group.
Engineering is split in two: 4 people in Bengaluru and 1 in Pune.

Group by an expression

Orders per month. DATE_FORMAT turns each date into a 'YYYY-MM' label, and the label is the group:

MySQL

SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,       COUNT(*) AS ordersFROM ordersGROUP BY monthORDER BY month;

ResultMySQL 8.4

month	orders
2025-01	2
2025-02	2
2025-03	2
2025-04	2
2025-05	1
2025-06	1

6 rows in set

Revenue per product category:

SQL

SELECT p.category,       SUM(oi.quantity)           AS units,       SUM(oi.quantity * p.price) AS revenueFROM order_items oiJOIN products p ON p.product_id = oi.product_idGROUP BY p.categoryORDER BY revenue DESC;

ResultMySQL 8.4

category	units	revenue
Furniture	7	50493
Electronics	7	16093
Stationery	24	5676

3 rows in set

How this query works, step by step

The join attaches the category and price to each of the 16 order lines.
The lines are grouped into Electronics, Furniture and Stationery.
For each category, SUM adds up units and quantity × price.

The ONLY_FULL_GROUP_BY rule

Every column in SELECT must either be in GROUP BY or be inside an aggregate. Otherwise MySQL cannot know which row’s value to show:

MySQL

SELECT dept_id, first_name, COUNT(*)FROM employeesGROUP BY dept_id;

ResultMySQL 8.4

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

How this query works, step by step

Department 1 has five employees, so “the” first_name of the group is undefined.
MySQL 8.4 refuses the query (error 1055). Older versions silently picked a random name.
Fix it by aggregating (GROUP_CONCAT(first_name)) or by grouping by first_name too.

SQL

SELECT dept_id, COUNT(*) AS staff, GROUP_CONCAT(first_name ORDER BY first_name) AS namesFROM employeesGROUP BY dept_id;

ResultMySQL 8.4

dept_id	staff	names
NULL	1	Meera
1	5	Amit,Asha,Karan,Priya,Rahul
2	3	Arjun,Neha,Vikram
3	2	Kavya,Rohan
4	1	Sneha

5 rows in set

WHERE vs HAVING

WHERE filters rows before grouping.
HAVING filters groups after grouping.

SQL

SELECT dept_id, COUNT(*) AS staffFROM employeesWHERE salary >= 50000GROUP BY dept_idHAVING COUNT(*) >= 2;

ResultMySQL 8.4

dept_id	staff
1	4
2	2

2 rows in set

Subtotals with WITH ROLLUP

WITH ROLLUP adds a total row after the groups:

MySQL

SELECT category, COUNT(*) AS products, SUM(stock) AS stockFROM productsGROUP BY category WITH ROLLUP;

ResultMySQL 8.4

category	products	stock
Electronics	3	165
Furniture	4	65
Stationery	2	550
NULL	9	780

4 rows in set

How this query works, step by step

The first three rows are the normal groups.
The extra last row has NULL in category and the totals over all products.
Use GROUPING(category) to tell a rollup NULL from a real NULL.

Logical order with GROUP BY

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Because SELECT runs after GROUP BY, aggregates are available to SELECT, HAVING and ORDER BY, but not to WHERE.

Exercise: Orders per customer

For each customer_id in orders, show the number of orders and the date of the latest order. Sort by the number of orders, highest first.

Expected resultMySQL 8.4

customer_id	orders	last_order
1	3	2025-04-28
2	2	2025-04-10
3	2	2025-05-15
5	2	2025-06-01
4	1	2025-03-08

5 rows in set

Hint 1

COUNT(*) and MAX(order_date) grouped by customer_id.

SQL

SELECT customer_id, COUNT(*) AS orders, MAX(order_date) AS last_orderFROM ordersGROUP BY customer_idORDER BY orders DESC, customer_id;

ResultMySQL 8.4

customer_id	orders	last_order
1	3	2025-04-28
2	2	2025-04-10
3	2	2025-05-15
5	2	2025-06-01
4	1	2025-03-08

5 rows in set

Exercise: Stock value per category

For each product category, show the total stock value (price * stock).

Expected resultMySQL 8.4

category	stock_value
Furniture	298335
Electronics	253335
Stationery	124450

3 rows in set

Hint 1

SUM(price * stock) grouped by category.

SQL

SELECT category, SUM(price * stock) AS stock_valueFROM productsGROUP BY categoryORDER BY stock_value DESC;

ResultMySQL 8.4

category	stock_value
Furniture	298335
Electronics	253335
Stationery	124450

3 rows in set

Quick quiz

1. How many rows does SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id return?

2. Which clause filters groups by an aggregate value?

3. Why does SELECT dept_id, first_name, COUNT(*) ... GROUP BY dept_id fail in MySQL 8?

4. What does WITH ROLLUP add?

GROUP BY FAQ

What does GROUP BY do in SQL?

It groups rows that have the same values in the listed columns, so aggregate functions such as COUNT and SUM return one result per group.

Can I GROUP BY multiple columns?

Yes. GROUP BY dept_id, city creates one group for each distinct combination of the two columns.

What is the difference between GROUP BY and ORDER BY?

GROUP BY combines rows into groups for aggregation. ORDER BY only sorts the final result. They are often used together.

Does GROUP BY sort the results?

Not reliably. MySQL 8 no longer sorts grouped results implicitly. Add ORDER BY when you need a specific order.