ORDER BY with aggregates

Concepts covered: Sorting Results

The engine builds groups before it sorts them. Execution starts with FROM and WHERE. GROUP BY and HAVING come next, then SELECT, and ORDER BY runs after SELECT with LIMIT last. By the time ORDER BY runs, each group is 1 row carrying its aggregates and the aliases the SELECT list gave them, so an aggregate is a sort key like any other column. The animation groups 6 orders by customer and computes a count and a total for each group before sorting the 3 groups by total. Predict the order before you play it: C3 has 1 order worth 300, C1 has 3 worth 270 together, C2 has 2 worth 120, so DESC puts C3 first and C2 last.

SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC

4 ways to name the sort key

You can sort by an alias, by the aggregate expression written again, by the position of the column in the SELECT list, or by an aggregate that the SELECT list doesn't show at all. All 4 produce the same order on the orders table. The alias reads best, the expression repeats itself but survives a renamed alias, the position breaks when someone reorders the SELECT list, and the unselected aggregate is for sorting by a figure you do not want to display.

Alias

ORDER BY the alias total_spent, which exists because SELECT runs before ORDER BY.

SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC

Expression

ORDER BY SUM(amount) repeats the aggregate; the engine computes it once and reuses it.

SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY SUM(amount) DESC

Position

ORDER BY 3 names the third selected column, total_spent, by its position.

SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY 3 DESC

Unselected

ORDER BY SUM(amount) sorts by a total the result never shows: C3, C1, C2 with only their counts.

SELECT
  customer_id,
  COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY SUM(amount) DESC

Position numbers refer to the SELECT list of the same query, so adding a column before total_spent silently changes what ORDER BY 3 sorts by.

Top N and ties

Ranking groups usually ends in LIMIT. ORDER BY total_spent DESC LIMIT 2 returns the 2 biggest customers, C3 first. The rows LIMIT cuts are decided entirely by the sort, and SQL leaves the order of tied rows undefined. If C1 and C2 had both spent 270, the engine could return either one beside C3, and a rerun on the same data could return the other. Add a second sort key that can't tie, the group key itself, and the result is the same on every run.

SELECT
  customer_id,
  SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC, customer_id
LIMIT 2

HAVING cannot see the alias

ORDER BY accepts the alias because SELECT has already run. HAVING runs before SELECT, so HAVING total_spent > 200 names a column that does not exist yet and the query is rejected. Inside HAVING, write the aggregate out, SUM(amount) > 200, and keep the alias for ORDER BY. The query below keeps C3 at 300 and C1 at 270 and drops C2.

SELECT
  customer_id,
  SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 200
ORDER BY total_spent DESC

Which customer_id is on the first row of this result?

SELECT
  customer_id,
  COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 150
ORDER BY order_count

• C1, the customer with the most orders in the table
• C2, the customer with the lowest total spent
• C3, the only surviving customer with 1 order
• No row, because SUM(amount) is not in the SELECT list

A top-N query over groups uses every clause from this section, in a fixed order. SELECT names the group key and its aggregate, GROUP BY builds the groups, HAVING filters them with the aggregate written out, ORDER BY ranks them by the alias with the group key as tiebreaker, and LIMIT cuts the ranked list.

A sales table records the region and revenue of each sale. Report the total revenue of the 3 highest-earning regions among those over 10,000, highest first, with region breaking ties.

About this lesson

This section is part of the Aggregating: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.