SQL · Aggregation

GROUP BY, HAVING, ORDER BY, and the order SQL actually runs

SQL is written SELECT-first but runs FROM-first. Once you see that, three classic interview "gotchas" stop being things you memorise. They just follow from the order.

Your win todayRecite the logical execution order and use it to explain why aliases fail in WHERE and why aggregates need HAVING.

Written order ≠ run order

Mnemonic: Frank Was Going Home So Order Lunch. Microsoft documents the full logical order as FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → TOP.^{[MS Learn]} The engine may physically run things differently, but results always behave as if it ran in this order.

Watch the rows shrink

Run this on the Lesson 3 sales data (table sales):

SELECT   region, SUM(revenue) AS total_rev, COUNT(*) AS n
FROM     sales
WHERE    product = 'Laptop'
GROUP BY region
HAVING   SUM(revenue) > 100000
ORDER BY total_rev DESC;

WHERE filters rows, and HAVING filters groups. The alias total_rev only exists from step 5, so ORDER BY (step 6) can use it and WHERE (step 2) can't.

Three gotchas, all from the order

1 · Alias in WHERE fails

WHERE total_rev > 100000 ❌
WHERE runs at step 2, and the alias is created at step 5. Repeat the expression instead. (ORDER BY can use the alias.)

2 · Aggregate in WHERE fails

WHERE SUM(revenue) > 100000 ❌
Groups don't exist yet at step 2. Use HAVING.

3 · Un-grouped column fails

SELECT region, rep, SUM(revenue) … GROUP BY region ❌
One row per region, but which rep? Every non-aggregated SELECT column must be in GROUP BY. (Older MySQL used to return an arbitrary rep. Say so if they ask about MySQL.)

ORDER BY basics

ORDER BY total_rev DESC, region ASC
ASC is the default. With several columns, the second breaks ties in the first. Sorting is the last step before LIMIT.

Say it in the interview"Logically, SQL runs FROM and JOINs first, then WHERE filters rows, GROUP BY forms groups, HAVING filters groups, SELECT computes the output columns, and finally ORDER BY sorts and LIMIT trims. That's why I can't use a SELECT alias or an aggregate in WHERE, but I can use the alias in ORDER BY. GROUP BY is SQL's pivot table: one output row per group."

Drill: click in run order

Click the clauses in the order SQL logically executes them.

Retrieval check

Why does WHERE total_rev > 100000 fail?

What does HAVING filter?

Which of these runs first?

How many rows does the example query return?

SELECT region, rep, SUM(revenue) FROM sales GROUP BY region. What happens?

Type it

Total units per product, highest first. Alias it total_units.

Complete SELECT region, COUNT(*) FROM sales GROUP BY region ______ to keep only regions with more than 2 orders.

Your turn

Load the 8-row sales table into sqliteonline.com (ask your teacher for the script).
Run the example query. Then delete clauses one by one from the bottom up and watch the row count grow back toward 8.
Deliberately write WHERE SUM(revenue) > 100000 and read the error message.
Rebuild your Lesson 3 pivot (Region × revenue) as a GROUP BY query and confirm the same numbers.
Write the run order on paper from memory, with the mnemonic.
Do the click drill above perfectly twice, then again tomorrow.

Go deeper

Primary source: Microsoft Learn: SELECT (Transact-SQL). Scroll to "Logical processing order of the SELECT statement". Practice: StrataScratch GROUP BY questions.

💬 Ask your teacher: "give me 5 GROUP BY interview questions" or "what about window functions?"