Aggregation & NULLs

Logical execution order explains most confusing SQL behaviour: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

Because SELECT runs after WHERE, you cannot filter on a column alias in WHERE — the alias does not exist yet. You can use it in ORDER BY, which runs later. That one ordering answers a lot of "why doesn't this work".

WHERE filters rows; HAVING filters groups. WHERE runs before grouping and cannot see aggregates. HAVING runs after and can. "Customers who placed more than five orders" is HAVING COUNT(*) > 5; "orders placed this year" is WHERE. Putting a row condition in HAVING usually still works and is slower, because you grouped rows you were about to discard.

COUNT(\*) and `COUNT(column)` are different. COUNT(*) counts rows. COUNT(column) counts non-NULL values in that column. COUNT(DISTINCT column) counts distinct non-NULL values. Reaching for the wrong one is how "how many users have a phone number" becomes "how many users".

NULL is unknown, not zero and not empty. NULL = NULL is not true — it is unknown — so comparisons need IS NULL. Aggregates skip NULLs, which means AVG(score) over ten rows with three NULLs divides by seven, not ten. Whether that is right depends entirely on whether a missing score means "no score" or "zero", and only you know.

Aggregating with no rows returns NULL, not 0, for SUM. COALESCE(SUM(x), 0) is usually what a report wants.

GROUP BY groups by every non-aggregated column you select. Most databases require them to be listed; the ones that do not will happily return an arbitrary row's value for the rest.