You write SQL top to bottom: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. The database does not run it in that order. Once you know the real order, a whole family of confusing errors starts to make sense.
The logical order of a query
FROMandJOIN: build the set of rows.WHERE: throw away rows that fail the condition.GROUP BY: collapse the remaining rows into groups.HAVING: throw away groups that fail the condition.SELECT: calculate the output columns (and window functions).DISTINCT: remove duplicate output rows.ORDER BY: sort.LIMIT/OFFSET: keep only some rows.
WHERE filters rows, HAVING filters groups
Imagine a fictional pharmacy chain, MediKart, with a bills table: store, bill_date, amount. The question: "Which stores had total sales above ₹5 lakh in October, counting only bills above ₹100?"
SELECT store, SUM(amount) AS total
FROM bills
WHERE bill_date >= '2026-10-01' AND bill_date < '2026-11-01'
AND amount > 100 -- row-level: each bill
GROUP BY store
HAVING SUM(amount) > 500000 -- group-level: each store's total
ORDER BY total DESC;The rule: if the condition is about a single row, put it in WHERE. If it is about an aggregate (SUM, COUNT, AVG…), it can only go in HAVING, because at WHERE time the groups do not exist yet.
Putting row conditions in WHERE rather than HAVING is also faster: the database groups fewer rows.
Why the alias does not work in WHERE
-- Error in most databases
SELECT store, amount * 0.18 AS gst
FROM bills
WHERE gst > 50;gst is created in step 5 (SELECT), but WHERE runs in step 2. At that point the alias does not exist. Repeat the expression (WHERE amount * 0.18 > 50) or calculate it in a CTE first.
Aliases do work in ORDER BY, because ORDER BY runs after SELECT. (Some databases also allow them in GROUP BY or HAVING as an extension, but do not rely on it in an interview.)
Every selected column must be grouped or aggregated
-- Error: bill_date is neither grouped nor aggregated
SELECT store, bill_date, SUM(amount)
FROM bills
GROUP BY store;After grouping, each store is one row. Which bill_date should that row show? There are many. Either add it to GROUP BY (one row per store per date) or aggregate it (MAX(bill_date) for the latest bill).
COUNT(*) vs COUNT(column)
COUNT(*)counts rows.COUNT(column)counts rows where that column is not NULL.COUNT(DISTINCT column)counts unique non-NULL values.
This difference is a favourite trick question. If 10 bills have 2 with a NULL customer_id, COUNT(*) is 10 and COUNT(customer_id) is 8.
Conditional aggregates with CASE
Often you want several filtered totals in one row per group. Instead of several queries, put the condition inside the aggregate:
SELECT store,
SUM(CASE WHEN amount >= 1000 THEN 1 ELSE 0 END) AS big_bills,
SUM(CASE WHEN amount < 1000 THEN 1 ELSE 0 END) AS small_bills
FROM bills
GROUP BY store;This "pivot with CASE" pattern appears in real reports all the time.
Practise
Run through aggregates and GROUP BY and then HAVING. If you can explain in one sentence why each query is written the way it is, you understand execution order. Next, learn joins and window functions.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: