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.

Practice

1. What does the GROUP BY clause do in an SQL query?

easy

A. It deletes duplicate rows from the table.

B. It sorts the rows in ascending order.

C. It groups rows that have the same values in specified columns.

D. It limits the number of rows returned.

Solution

1. Step 1: Understand the purpose of GROUP BY

   The GROUP BY clause collects rows with the same values in specified columns into summary rows.

2. Step 2: Differentiate from other clauses

   ORDER BY sorts rows, DELETE removes rows, and LIMIT restricts output count, which are different from grouping.

3. Final Answer:

   It groups rows that have the same values in specified columns. -> Option C

4. Quick Check:

   GROUP BY = grouping rows [OK]

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

Common Mistakes:

• Confusing GROUP BY with ORDER BY
• Thinking GROUP BY deletes duplicates
• Assuming GROUP BY limits rows

2. Which of the following SQL queries correctly groups sales by product and orders the result by total sales descending?

easy

A. SELECT product, SUM(amount) FROM sales GROUP BY product ORDER BY SUM(amount) DESC;

B. SELECT product, SUM(amount) FROM sales ORDER BY product GROUP BY SUM(amount) DESC;

C. SELECT product, SUM(amount) FROM sales GROUP BY product ORDER BY product DESC;

D. SELECT product, SUM(amount) FROM sales ORDER BY SUM(amount) GROUP BY product DESC;

Solution

1. Step 1: Check GROUP BY and ORDER BY order

   The correct syntax is GROUP BY first, then ORDER BY to sort grouped results.

2. Step 2: Verify ordering by aggregated column

   Ordering by SUM(amount) DESC sorts by total sales descending, matching the requirement.

3. Final Answer:

   SELECT product, SUM(amount) FROM sales GROUP BY product ORDER BY SUM(amount) DESC; -> Option A

4. Quick Check:

   GROUP BY then ORDER BY with aggregate [OK]

Hint: GROUP BY before ORDER BY; order by aggregate for totals [OK]

Common Mistakes:

• Swapping GROUP BY and ORDER BY order
• Ordering by non-aggregated columns incorrectly
• Using ORDER BY before GROUP BY

3. Given the table orders with columns customer_id and order_total, what will this query return?

SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id ORDER BY order_count ASC;

medium

A. Syntax error due to incorrect ORDER BY usage.

B. List of customers with their order counts sorted from highest to lowest.

C. List of customers with total order amounts sorted ascending.

D. List of customers with their order counts sorted from lowest to highest.

Solution

1. Step 1: Understand the GROUP BY and COUNT

   The query groups rows by customer_id and counts orders per customer.

2. Step 2: Analyze ORDER BY order_count ASC

   Ordering by order_count ascending sorts customers from fewest to most orders.

3. Final Answer:

   List of customers with their order counts sorted from lowest to highest. -> Option D

4. Quick Check:

   ORDER BY ASC sorts ascending [OK]

Hint: ORDER BY ASC sorts from smallest to largest [OK]

Common Mistakes:

• Assuming ORDER BY ASC sorts descending
• Confusing COUNT with SUM
• Thinking query returns total order amounts

4. Identify the error in this SQL query:

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

medium

A. GROUP BY must come before ORDER BY in the query.

B. COUNT(*) cannot be used in ORDER BY clause.

C. Missing alias for COUNT(*) causes syntax error.

D. ORDER BY cannot sort aggregated columns.

Solution

1. Step 1: Check SQL clause order

   GROUP BY must appear before ORDER BY in SQL syntax.

2. Step 2: Identify the error in clause sequence

   The query places ORDER BY before GROUP BY, causing syntax error.

3. Final Answer:

   GROUP BY must come before ORDER BY in the query. -> Option A

4. Quick Check:

   GROUP BY before ORDER BY [OK]

Hint: GROUP BY always before ORDER BY in SQL [OK]

Common Mistakes:

• Placing ORDER BY before GROUP BY
• Assuming COUNT(*) can't be in ORDER BY
• Forgetting clause order rules

5. You have a sales table with columns region, salesperson, and amount. You want to find the total sales per region, but only show regions with total sales above 1000, sorted by total sales descending. Which query achieves this?

hard

A. SELECT region, SUM(amount) FROM sales WHERE SUM(amount) > 1000 GROUP BY region ORDER BY SUM(amount) DESC;

B. SELECT region, SUM(amount) FROM sales GROUP BY region HAVING SUM(amount) > 1000 ORDER BY SUM(amount) DESC;

C. SELECT region, SUM(amount) FROM sales GROUP BY region ORDER BY SUM(amount) DESC HAVING SUM(amount) > 1000;

D. SELECT region, SUM(amount) FROM sales ORDER BY SUM(amount) DESC HAVING SUM(amount) > 1000 GROUP BY region;

Solution

1. Step 1: Use GROUP BY to group sales by region

   Grouping by region allows aggregation of sales per region.

2. Step 2: Filter groups with HAVING clause

   HAVING filters groups after aggregation; WHERE cannot filter on aggregates.

3. Step 3: Sort results with ORDER BY descending

   ORDER BY SUM(amount) DESC sorts regions by total sales from highest to lowest.

4. Final Answer:

   SELECT region, SUM(amount) FROM sales GROUP BY region HAVING SUM(amount) > 1000 ORDER BY SUM(amount) DESC; -> Option B

5. Quick Check:

   GROUP BY + HAVING + ORDER BY correct order [OK]

Hint: Use HAVING to filter grouped results, ORDER BY last [OK]

Common Mistakes:

• Using WHERE to filter aggregated results
• Placing HAVING after ORDER BY
• Incorrect clause order