GROUP BY clause in MySQL - Step-by-Step Execution

Concept Flow - GROUP BY clause

Start with SELECT query

↓

Identify columns to group by

↓

Scan table rows

↓

Group rows by unique values in GROUP BY columns

↓

Apply aggregate functions (SUM, COUNT, AVG, etc.) per group

↓

Return one result row per group

↓

End

The GROUP BY clause groups rows sharing the same values in specified columns, then aggregates data per group, returning one row per group.

Execution Sample

MySQL

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

This query counts how many employees are in each department by grouping rows by department.

Execution Table

Step	Action	Current Row Data	Group Key	Groups Formed	Aggregate Calculation	Output Row
1	Read row	{id:1, department:'Sales'}	Sales	{Sales}	COUNT(Sales)=1
2	Read row	{id:2, department:'HR'}	HR	{Sales, HR}	COUNT(Sales)=1, COUNT(HR)=1
3	Read row	{id:3, department:'Sales'}	Sales	{Sales, HR}	COUNT(Sales)=2, COUNT(HR)=1
4	Read row	{id:4, department:'IT'}	IT	{Sales, HR, IT}	COUNT(Sales)=2, COUNT(HR)=1, COUNT(IT)=1
5	Read row	{id:5, department:'HR'}	HR	{Sales, HR, IT}	COUNT(Sales)=2, COUNT(HR)=2, COUNT(IT)=1
6	End of rows			{Sales, HR, IT}	Final counts: Sales=2, HR=2, IT=1	Sales, 2; HR, 2; IT, 1

💡 All rows processed; groups formed by department; aggregates calculated per group.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
Groups	{}	{Sales}	{Sales, HR}	{Sales, HR}	{Sales, HR, IT}	{Sales, HR, IT}	{Sales, HR, IT}
COUNT(Sales)	0	1	1	2	2	2	2
COUNT(HR)	0	0	1	1	1	2	2
COUNT(IT)	0	0	0	0	1	1	1

Key Moments - 2 Insights

Why does the output show one row per department instead of one row per employee?

Because the GROUP BY clause groups all rows with the same department value together, so the output has one row per unique department, as shown in execution_table row 6.

What happens if we include a column in SELECT that is not in GROUP BY or an aggregate function?

MySQL will give an error or unpredictable results because every selected column must be either grouped or aggregated. This is why in the example only 'department' (grouped) and COUNT(*) (aggregated) are selected.

Visual Quiz - 3 Questions

Look at the execution_table at step 3, what is the count of employees in the 'Sales' group?

A 1

B 3

C 2

D 0

💡 Hint

Check the 'Aggregate Calculation' column at step 3 for COUNT(Sales).

At which step does the 'IT' group first appear in the groups formed?

A Step 4

B Step 2

C Step 3

D Step 5

💡 Hint

Look at the 'Groups Formed' column to see when 'IT' is added.

If a new employee with department 'Sales' is added, how would the final COUNT(Sales) change?

A It would stay the same

B It would increase by 1

C It would decrease by 1

D It would reset to 0

💡 Hint

Refer to the variable_tracker for COUNT(Sales) increments per row.

Concept Snapshot

GROUP BY clause groups rows by specified columns.
Aggregates like COUNT, SUM apply per group.
Output returns one row per group.
All selected columns must be grouped or aggregated.
Useful for summarizing data by categories.

Full Transcript

The GROUP BY clause in SQL groups rows that have the same values in specified columns. For example, grouping employees by their department. The database reads each row, identifies the group key (like department name), and collects rows into groups. Then it applies aggregate functions such as COUNT to each group to summarize data. The output shows one row per group with the aggregated results. This process stops after all rows are processed. Beginners often wonder why output rows are fewer than input rows; it's because rows are combined into groups. Also, every column in SELECT must be either grouped or aggregated to avoid errors. This visual trace shows step-by-step how groups form and counts update as rows are read.