Three flavours of the same confusion. (1) A per-store report shows a store paired with
an order id or date that does not belong to it. (2) SQLite runs your grouped query
happily, but the same query at work explodes with column "x" must appear in the GROUP BY clause (Postgres) or an ONLY_FULL_GROUP_BY error (MySQL). (3) A filter on a
total fails with misuse of aggregate: SUM() — or runs but returns the wrong stores.
First grouped reports — counts and totals per store, per day, per route. The trap is sharpened by practising on SQLite, which is lenient where production engines are strict: what "worked" in the lesson errors or lies at the office. Linked module: 0.6 (SQL for AI people).
Two rules cover all three flavours. Rule one: after GROUP BY, each group becomes ONE
output row, so every selected column must either be in the GROUP BY or be wrapped in
an aggregate — otherwise there is no single honest value to print. Strict engines
refuse; SQLite silently picks the value from an arbitrary row in the group, which is
why the report pairs a store with someone else's order id. Rule two: WHERE runs before
grouping and can only see raw rows; HAVING runs after and can see the finished
aggregates. WHERE SUM(amount) > 500 is therefore impossible — no row has a SUM yet —
while a raw-row condition in HAVING wastes work and confuses readers. Confirm flavour
(1) by re-running the query in a strict engine or checking whether the bare column is
functionally tied to the group key; confirm flavour (3) by reading which clause holds
the aggregate.
For bare columns: either add the column to the GROUP BY (if you truly want one group
per combination) or wrap it in an aggregate — MAX(date), COUNT(order_id) — that states
which value you mean. For misplaced filters: move aggregate conditions into HAVING and
row conditions into WHERE, so "PAID orders only, stores over 500" becomes
WHERE status = 'PAID' GROUP BY store_id HAVING SUM(amount) > 500. Re-check the
result by totalling the groups and comparing against a plain SUM over the same WHERE.
Write grouped queries in execution order in your head: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY — each clause only uses what the previous one produced. Before adding any column to SELECT, ask "is this the key, or an aggregate?" — if neither, stop. Cross-check every grouped report once: the sum of the group totals must equal the ungrouped total under the same WHERE.
Q: "Explain the difference between WHERE and HAVING, with an example of a query that needs both." Strong answer: WHERE filters rows before grouping, HAVING filters groups after aggregation, delivered with the PAID-orders-over-500 example in one line of SQL. Distinction-level answers add the bare-column rule — every selected column is either grouped or aggregated — and note that SQLite hides the violation while Postgres and MySQL raise, which is why totals should be cross-checked when practising locally.