GROUP BY with multiple columns in MySQL - Step-by-Step Execution

Concept Flow - GROUP BY with multiple columns

Start with SELECT query

↓

Identify columns to GROUP BY

↓

Sort rows by first column

↓

Within first column groups, sort by second column

↓

Aggregate rows in each group

↓

Return grouped result set

The query groups rows first by the first column, then by the second column, aggregating data within each group.

Execution Sample

MySQL

SELECT department, role, COUNT(*) AS count
FROM employees
GROUP BY department, role;

This query counts employees grouped by both department and role.

Execution Table

Step	Row Data	Group Key (department, role)	Action	Aggregate Count
1	{department: 'Sales', role: 'Manager'}	('Sales', 'Manager')	Create new group	1
2	{department: 'Sales', role: 'Salesperson'}	('Sales', 'Salesperson')	Create new group	1
3	{department: 'Sales', role: 'Manager'}	('Sales', 'Manager')	Add to existing group	2
4	{department: 'HR', role: 'Manager'}	('HR', 'Manager')	Create new group	1
5	{department: 'HR', role: 'Recruiter'}	('HR', 'Recruiter')	Create new group	1
6	{department: 'HR', role: 'Manager'}	('HR', 'Manager')	Add to existing group	2
7	{department: 'Sales', role: 'Salesperson'}	('Sales', 'Salesperson')	Add to existing group	2
8	No more rows	-	Finish grouping	-

💡 All rows processed and grouped by department and role.

Variable Tracker

Group Key	Start	After 1	After 2	After 3	After 4	After 5	After 6	After 7	Final
('Sales', 'Manager')	0	1	1	2	2	2	2	2	2
('Sales', 'Salesperson')	0	0	1	1	1	1	1	2	2
('HR', 'Manager')	0	0	0	0	1	1	2	2	2
('HR', 'Recruiter')	0	0	0	0	0	1	1	1	1

Key Moments - 3 Insights

Why does the count for ('Sales', 'Manager') increase only on certain steps?

Because only rows with department 'Sales' and role 'Manager' add to that group, as shown in steps 1 and 3 in the execution_table.

What happens when a new combination of department and role appears?

A new group is created for that combination, as seen in steps 2, 4, and 5 where new groups start.

Why do some groups have the same count after multiple steps?

Because no new rows matching those group keys were processed in those steps, so the count stays the same.

Visual Quiz - 3 Questions

Look at the execution_table, what is the aggregate count for ('HR', 'Manager') after step 6?

A 1

B 3

C 2

D 0

💡 Hint

Check the 'Aggregate Count' column for step 6 in the execution_table.

At which step does the group ('Sales', 'Salesperson') first get created?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Look for the first 'Create new group' action with group key ('Sales', 'Salesperson') in the execution_table.

If a new row with {department: 'HR', role: 'Manager'} is added after step 7, what would happen to the count?

A Count for ('Sales', 'Manager') increases by 1

B Count for ('HR', 'Manager') increases by 1

C A new group is created

D No change in any count

💡 Hint

Refer to variable_tracker for ('HR', 'Manager') and how counts increase when matching rows are added.

Concept Snapshot

GROUP BY with multiple columns syntax:
SELECT columns, aggregate_function()
FROM table
GROUP BY column1, column2;

Groups rows first by column1, then by column2.
Aggregates data within each unique combination of these columns.

Full Transcript

This visual execution shows how a SQL query with GROUP BY on multiple columns works. The query groups rows by department and role. Each row is checked, and if its department and role combination is new, a new group is created. If the combination exists, the row is added to that group, increasing the count. The execution table tracks each step, showing how groups form and counts increase. The variable tracker summarizes counts for each group after each step. Key moments clarify common confusions about grouping and counting. The quiz tests understanding of group creation and count updates. The snapshot summarizes the syntax and behavior of GROUP BY with multiple columns.