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.

Practice

1. What is the main purpose of the GROUP BY clause in a MySQL query?

easy

A. To filter rows based on a condition

B. To sort the result set in ascending order

C. To join two or more tables together

D. To group rows that have the same values in specified columns

Solution

1. Step 1: Understand the role of GROUP BY

   The GROUP BY clause groups rows that share the same values in one or more columns.

2. Step 2: Differentiate from other clauses

   Sorting is done by ORDER BY, filtering by WHERE, and joining by JOIN. GROUP BY is specifically for grouping rows.

3. Final Answer:

   To group rows that have the same values in specified columns -> Option D

4. Quick Check:

   GROUP BY = group rows by column values [OK]

Hint: GROUP BY groups rows by column values [OK]

Common Mistakes:

• Confusing GROUP BY with ORDER BY
• Using GROUP BY to filter rows
• Thinking GROUP BY joins tables

2. Which of the following is the correct syntax to group rows by the column department in a MySQL query?

easy

A. SELECT * FROM employees GROUP BY department;

B. SELECT * FROM employees GROUP department BY;

C. SELECT * FROM employees ORDER BY department GROUP;

D. SELECT * FROM employees WHERE department GROUP BY;

Solution

1. Step 1: Recall correct GROUP BY syntax

   The correct syntax is: SELECT columns FROM table GROUP BY column_name;

2. Step 2: Check each option

   SELECT * FROM employees GROUP BY department; matches the correct syntax exactly. Others have misplaced keywords or wrong order.

3. Final Answer:

   SELECT * FROM employees GROUP BY department; -> Option A

4. Quick Check:

   GROUP BY column_name syntax = SELECT * FROM employees GROUP BY department; [OK]

Hint: GROUP BY follows SELECT ... FROM ... GROUP BY column [OK]

Common Mistakes:

• Placing GROUP BY after WHERE clause incorrectly
• Swapping GROUP BY and ORDER BY keywords
• Using GROUP BY without specifying columns

3. Given the table sales with columns region and amount, what will the following query return?

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

medium

A. Total sales amount for each region

B. List of all sales amounts without grouping

C. Syntax error due to missing alias

D. Average sales amount for each region

Solution

1. Step 1: Understand the query components

   The query groups rows by region and sums the amount for each group.

2. Step 2: Interpret the result

   It returns one row per region with the total sales amount summed up.

3. Final Answer:

   Total sales amount for each region -> Option A

4. Quick Check:

   GROUP BY + SUM = total per group [OK]

Hint: SUM with GROUP BY gives total per group [OK]

Common Mistakes:

• Thinking it returns average instead of sum
• Expecting all rows without grouping
• Assuming syntax error without alias

4. Identify the error in this query:

SELECT department, COUNT(employee_id) FROM employees WHERE salary > 50000 GROUP BY employee_id;

medium

A. COUNT cannot be used with employee_id

B. GROUP BY should use department, not employee_id

C. WHERE clause cannot be used with GROUP BY

D. Missing ORDER BY clause

Solution

1. Step 1: Analyze GROUP BY usage

   The query groups by employee_id but selects department, which causes mismatch.

2. Step 2: Correct grouping column

   To count employees per department, GROUP BY should be on department, matching the selected column.

3. Final Answer:

   GROUP BY should use department, not employee_id -> Option B

4. Quick Check:

   GROUP BY column must match selected non-aggregated columns [OK]

Hint: GROUP BY columns must match selected non-aggregated columns [OK]

Common Mistakes:

• Grouping by wrong column not in SELECT
• Thinking WHERE conflicts with GROUP BY
• Assuming ORDER BY is mandatory

5. You have a products table with columns category, price, and stock. Which query correctly finds the average price and total stock for each category?

hard

A. SELECT category, AVG(price), SUM(stock) FROM products GROUP BY price;

B. SELECT category, price, stock FROM products GROUP BY category;

C. SELECT category, AVG(price), SUM(stock) FROM products GROUP BY category;

D. SELECT category, AVG(price), SUM(stock) FROM products;

Solution

1. Step 1: Identify aggregation needs

   We want average price and total stock per category, so use AVG(price) and SUM(stock).

2. Step 2: Group by category

   To get results per category, GROUP BY category is required.

3. Step 3: Check each option

   SELECT category, AVG(price), SUM(stock) FROM products GROUP BY category; correctly uses GROUP BY category with AVG and SUM. Others miss grouping or group by wrong column.

4. Final Answer:

   SELECT category, AVG(price), SUM(stock) FROM products GROUP BY category; -> Option C

5. Quick Check:

   Aggregate functions + GROUP BY category = SELECT category, AVG(price), SUM(stock) FROM products GROUP BY category; [OK]

Hint: Use GROUP BY category with AVG and SUM for correct aggregation [OK]

Common Mistakes:

• Grouping by wrong column like price
• Not using GROUP BY with aggregate functions
• Selecting columns without aggregation or grouping