GROUP BY with ORDER BY in SQL - Step-by-Step Execution

Concept Flow - GROUP BY with ORDER BY

Start with Table Data

↓

Apply GROUP BY

↓

Aggregate rows into groups

↓

Calculate aggregate values per group

↓

Apply ORDER BY on grouped results

↓

Return sorted grouped results

The query groups rows by specified columns, calculates aggregates per group, then sorts the grouped results.

Execution Sample

SQL

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

Groups employees by department, counts employees per department, then orders departments by count descending.

Execution Table

Step	Action	Input Data	Group Formed	Aggregate Calculated	Order Applied	Output
1	Read all rows from employees table	[{dept: 'HR'}, {dept: 'IT'}, {dept: 'HR'}, {dept: 'IT'}, {dept: 'Sales'}]	N/A	N/A	N/A	All rows ready for grouping
2	Group rows by department	All rows	{HR: 2 rows, IT: 2 rows, Sales: 1 row}	N/A	N/A	Groups formed
3	Count employees in each group	Groups	Same groups	{HR: 2, IT: 2, Sales: 1}	N/A	Aggregates calculated
4	Order groups by employee_count DESC	Aggregates	Same groups	Same counts	HR(2), IT(2), Sales(1)	Sorted grouped results
5	Return final result	Sorted groups	Same groups	Same counts	Same order	[{department: 'HR', employee_count: 2}, {department: 'IT', employee_count: 2}, {department: 'Sales', employee_count: 1}]
6	End	N/A	N/A	N/A	N/A	Query complete

💡 All rows processed, groups formed, aggregates calculated, and results ordered.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
groups	N/A	{HR: 2 rows, IT: 2 rows, Sales: 1 row}	{HR: 2 rows, IT: 2 rows, Sales: 1 row}	{HR: 2 rows, IT: 2 rows, Sales: 1 row}	{HR: 2 rows, IT: 2 rows, Sales: 1 row}
employee_count	N/A	N/A	{HR: 2, IT: 2, Sales: 1}	{HR: 2, IT: 2, Sales: 1}	{HR: 2, IT: 2, Sales: 1}
ordered_result	N/A	N/A	N/A	[HR(2), IT(2), Sales(1)]	[{department: 'HR', employee_count: 2}, {department: 'IT', employee_count: 2}, {department: 'Sales', employee_count: 1}]

Key Moments - 2 Insights

Why do we apply ORDER BY after GROUP BY and not before?

ORDER BY sorts the grouped results, so it must come after GROUP BY which creates those groups. See execution_table rows 3 and 4 where aggregation happens before ordering.

What happens if we order by a column not in GROUP BY or aggregate?

SQL will give an error because ORDER BY must use grouped columns or aggregates. This is shown by the need to order by employee_count, an aggregate, in execution_table row 4.

Visual Quiz - 3 Questions

Look at the execution_table, what is the employee_count for the IT group after Step 3?

A 1

B 3

C 2

D 0

💡 Hint

Check the 'Aggregate Calculated' column at Step 3 for the IT group.

At which step is the ORDER BY applied in the execution_table?

A Step 4

B Step 3

C Step 2

D Step 5

💡 Hint

Look for the 'Order Applied' column showing sorting action.

If the ORDER BY was changed to ascending, how would the final output order change?

A HR, IT, Sales

B Sales, HR, IT

C IT, HR, Sales

D Sales, IT, HR

💡 Hint

Refer to variable_tracker 'ordered_result' and reverse the order of employee_count.

Concept Snapshot

GROUP BY groups rows by column(s).
Aggregates like COUNT calculate per group.
ORDER BY sorts the grouped results.
ORDER BY must use grouped columns or aggregates.
Syntax: SELECT cols, AGG() FROM table GROUP BY cols ORDER BY col;

Full Transcript

This visual execution shows how a SQL query with GROUP BY and ORDER BY works step-by-step. First, all rows from the employees table are read. Then rows are grouped by the department column. Next, the COUNT aggregate calculates the number of employees in each department group. After aggregation, the results are ordered by employee_count in descending order. Finally, the sorted grouped results are returned. Key points include that ORDER BY happens after grouping and aggregation, and it must use grouped columns or aggregates. The variable tracker shows how groups and counts evolve through the steps. The quiz questions help reinforce understanding of when ordering happens and how counts are calculated.