GROUP BY multiple columns in SQL - Step-by-Step Execution

Concept Flow - GROUP BY multiple columns

Start with table data

↓

Select columns to group by

↓

Group rows by unique combinations of these columns

↓

Aggregate data within each group

↓

Return grouped result set

The query groups rows by unique combinations of multiple columns, then aggregates data within each group.

Execution Sample

SQL

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

Groups employees by department and role, counting how many employees are in each group.

Execution Table

Step	Action	Current Grouping	Rows in Group	Aggregate Result
1	Start with all rows	None	All 9 rows	None
2	Group by department='Sales', role='Manager'	('Sales', 'Manager')	2 rows	Count=2
3	Group by department='Sales', role='Associate'	('Sales', 'Associate')	2 rows	Count=2
4	Group by department='HR', role='Manager'	('HR', 'Manager')	1 row	Count=1
5	Group by department='HR', role='Associate'	('HR', 'Associate')	1 row	Count=1
6	Group by department='IT', role='Developer'	('IT', 'Developer')	2 rows	Count=2
7	Group by department='IT', role='Manager'	('IT', 'Manager')	1 row	Count=1
8	Return all grouped results	All groups	Each group's rows	Counts per group

💡 All unique combinations of department and role processed, aggregation complete.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	After Step 7	Final
Current Group	None	('Sales', 'Manager')	('Sales', 'Associate')	('HR', 'Manager')	('HR', 'Associate')	('IT', 'Developer')	('IT', 'Manager')	All groups processed
Count	N/A	2	2	1	1	2	1	Final counts per group

Key Moments - 2 Insights

Why do we group by both department and role instead of just one?

Grouping by both columns creates groups for each unique combination, as shown in execution_table rows 2-7. Grouping by only one column would mix different roles together.

What does COUNT(*) count in each group?

COUNT(*) counts the number of rows in each group, as seen in the 'Aggregate Result' column in execution_table rows 2-7.

Visual Quiz - 3 Questions

Look at the execution_table, what is the count for the group ('IT', 'Developer')?

A 1

B 3

C 2

D 0

💡 Hint

Check the 'Aggregate Result' column at Step 6 in the execution_table.

At which step does the grouping for ('HR', 'Associate') happen?

A Step 3

B Step 5

C Step 4

D Step 6

💡 Hint

Look at the 'Current Grouping' column in the execution_table.

If we remove 'role' from GROUP BY, what happens to the groups?

A Groups combine by department only

B Groups combine by role only

C Groups stay the same

D No groups are formed

💡 Hint

Refer to key_moments about grouping by multiple columns.

Concept Snapshot

GROUP BY multiple columns syntax:
SELECT col1, col2, aggregate_function()
FROM table
GROUP BY col1, col2;

Groups rows by unique combinations of col1 and col2,
then applies aggregation per group.
Useful to analyze data by multiple categories.

Full Transcript

This visual execution shows how SQL groups rows by multiple columns. Starting with all rows, the query groups them by unique combinations of department and role. Each group contains rows sharing the same values in both columns. Then, an aggregate function like COUNT(*) counts rows in each group. The execution table traces each group formed and the count result. Variable tracking shows how the current group and count change step-by-step. Key moments clarify why grouping by multiple columns creates more specific groups and what COUNT(*) counts. The quiz tests understanding of group counts, step order, and effects of changing GROUP BY columns. The snapshot summarizes syntax and behavior for quick reference.