A SQL query doesn't run top-to-bottom in the order it's typed. It runs in a fixed logical order — roughly FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY — and WHERE finishes its job well before GROUP BY ever creates a single aggregate. Writing a condition on SUM() or COUNT() into WHERE isn't a style choice with a minor downside; it's asking a clause to filter on a value that doesn't exist yet at the point it runs.
Verified by actually running this against SQLite — not hand-computed. Seven line items across two categories and four products.
CREATE TABLE order_items(
order_id INTEGER,
category TEXT,
product TEXT,
quantity INTEGER
);
INSERT INTO order_items VALUES
(1,'Electronics','Headphones',40),
(2,'Electronics','Headphones',70),
(3,'Electronics','Charger',30),
(4,'Electronics','Charger',20),
(5,'Kitchen','Blender',80),
(6,'Kitchen','Blender',50),
(7,'Kitchen','Toaster',15);| order_id | category | product | quantity |
|---|---|---|---|
| 1 | Electronics | Headphones | 40 |
| 2 | Electronics | Headphones | 70 |
| 3 | Electronics | Charger | 30 |
| 4 | Electronics | Charger | 20 |
| 5 | Kitchen | Blender | 80 |
| 6 | Kitchen | Blender | 50 |
| 7 | Kitchen | Toaster | 15 |
The goal: find Electronics products that have sold more than 100 units in total — Headphones (40 + 70 = 110) qualifies, Charger (30 + 20 = 50) doesn't. Kitchen's Blender also totals over 100 (80 + 50 = 130), but it's a different category and shouldn't appear in an Electronics report.
"Total quantity over 100" sounds like exactly what a WHERE clause is for:
SELECT product, SUM(quantity) AS total_qty
FROM order_items
WHERE SUM(quantity) > 100
GROUP BY product;Error: misuse of aggregate: SUM()This one doesn't return a wrong number — it doesn't run at all. Every mainstream SQL engine rejects an aggregate function inside WHERE with some version of this error. That's actually the forgiving outcome: a query that fails loudly is far easier to catch than one that quietly computes the wrong thing.
WHERE filters individual rows coming out of FROM, one at a time, before those rows have been grouped into anything. SUM(quantity) is a question about a whole group of rows — it can't be answered until GROUP BY has actually formed the groups, which happens in a later stage. Asking WHERE to evaluate SUM(quantity) > 100 is asking for a group total at a point in execution where no groups exist yet; there's nothing to sum.
SELECT product, SUM(quantity) AS total_qty
FROM order_items
WHERE category = 'Electronics'
GROUP BY product
HAVING SUM(quantity) > 100;| product | total_qty |
|---|---|
| Headphones | 110 |
HAVING exists for exactly this: it runs after GROUP BY, once every group's aggregates are already computed, so SUM(quantity) > 100 has an actual number to compare against. Notice category = 'Electronics' stayed in WHERE — that condition doesn't need an aggregate, so it belongs in the clause that runs earliest. Kitchen's Blender, despite also totaling over 100, never even makes it to the grouping stage because WHERE already removed every Kitchen row first.
The rule isn't "aggregate conditions go in HAVING, everything else is optional" — it's that each clause should carry exactly the conditions it's capable of expressing, and no later than necessary. category = 'Electronics' is a fact about a single row; it belongs in WHERE, which discards non-matching rows before the database does the work of grouping and aggregating them. SUM(quantity) > 100 is a fact about a group; it can only be expressed in HAVING, because no earlier clause has a group to ask about. Moving a row-level condition into HAVING "works" in the sense that it still returns the right answer, but it means the database aggregates rows it was always going to throw away — for one table this is invisible, on a large one it's wasted work with no upside.
| Logical order | Clause | Can reference an aggregate? |
|---|---|---|
| 1 | FROM / JOIN | — |
| 2 | WHERE | No — runs before grouping exists |
| 3 | GROUP BY | — |
| 4 | HAVING | Yes — this is what it's for |
| 5 | SELECT | Yes |
| 6 | ORDER BY | Yes |
This is one of the few traps on this site that fails loudly instead of quietly — SQLite refuses to run it at all. The real lesson isn't just "remember to use HAVING," it's that every SQL clause has a fixed place in an invisible pipeline, and a condition can only reference what's already been computed by the time its clause runs. Once that order is second nature, WHERE vs HAVING stops being a rule to memorize and becomes obvious from the shape of the question being asked.