GROUP BY with aggregate functions in SQL - Step-by-Step Execution

Concept Flow - GROUP BY with aggregate functions

Start with table data

↓

Group rows by column(s)

↓

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

↓

Return one row per group with aggregated values

↓

End with grouped result set

The query groups rows by specified columns, then calculates aggregate values for each group, returning one summary row per group.

Execution Sample

SQL

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

Counts how many employees are in each department by grouping rows by department.

Execution Table

Step	Action	Group formed	Aggregate calculation	Output row
1	Read all rows from employees table	None yet	None yet	None yet
2	Group rows by department	Sales: 3 rows, HR: 2 rows, IT: 2 rows	None yet	None yet
3	Calculate COUNT(*) for Sales group	Sales	COUNT = 3	('Sales', 3)
4	Calculate COUNT(*) for HR group	HR	COUNT = 2	('HR', 2)
5	Calculate COUNT(*) for IT group	IT	COUNT = 2	('IT', 2)
6	Return all grouped rows with counts	All groups	All counts calculated	[('Sales', 3), ('HR', 2), ('IT', 2)]
7	End of query execution	All groups processed	All aggregates done	Final result set returned

💡 All groups processed and aggregate counts calculated for each group.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	Final
groups	None	{Sales:3 rows, HR:2 rows, IT:2 rows}	{Sales:3}	{Sales:3, HR:2}	{Sales:3, HR:2, IT:2}	{Sales:3, HR:2, IT:2}
aggregates	None	None	{Sales:3}	{Sales:3, HR:2}	{Sales:3, HR:2, IT:2}	{Sales:3, HR:2, IT:2}
output_rows	None	None	[('Sales', 3)]	[('Sales', 3), ('HR', 2)]	[('Sales', 3), ('HR', 2), ('IT', 2)]	[('Sales', 3), ('HR', 2), ('IT', 2)]

Key Moments - 3 Insights

Why does the query return fewer rows than the original table?

Because the GROUP BY groups multiple rows into one per group, the output has one row per unique group value, as shown in execution_table rows 3-6.

What happens if we use an aggregate function without GROUP BY?

The aggregate function applies to the whole table as one group, returning a single summary row, unlike the multiple groups shown in the execution_table.

Can we select columns not in GROUP BY or aggregate functions?

No, SQL requires all selected columns to be either grouped or aggregated, otherwise it causes an error. This is why only 'department' and COUNT(*) appear in the example.

Visual Quiz - 3 Questions

Look at the execution_table, what is the COUNT(*) for the HR group at step 4?

A 2

B 3

C 1

D 0

💡 Hint

Check the 'Aggregate calculation' column at step 4 in the execution_table.

At which step does the query return the final grouped result set?

A Step 2

B Step 3

C Step 6

D Step 1

💡 Hint

Look for the step where 'Return all grouped rows with counts' happens in the execution_table.

If the employees table had a new department 'Marketing' with 4 employees, how would the output change?

A The output would remain the same

B A new group 'Marketing' with COUNT 4 would appear

C The counts for existing groups would increase

D The query would fail

💡 Hint

Refer to variable_tracker showing how groups and counts form per department.

Concept Snapshot

GROUP BY groups rows by specified columns.
Aggregate functions (COUNT, SUM, AVG, etc.) calculate summary values per group.
Result returns one row per group with aggregated data.
All selected columns must be grouped or aggregated.
Useful for summarizing data by categories.

Full Transcript

This visual execution trace shows how a SQL query with GROUP BY and aggregate functions works. First, the database reads all rows from the table. Then it groups rows by the specified column, here 'department'. Next, it calculates aggregate values like COUNT for each group. Finally, it returns one row per group with the aggregated results. The execution table tracks each step, showing groups formed and counts calculated. The variable tracker shows how groups and aggregates build up over steps. Key moments clarify common confusions like why output rows are fewer than input rows and the need for grouping or aggregation in SELECT. The quiz tests understanding of counts per group, when results are returned, and how adding data affects output. The snapshot summarizes the concept simply for quick review.