🪄HAVING
Filter groups, not raw rows
WHERE runs before grouping and cannot reference aggregate results from the same SELECT list. HAVING runs after aggregates and filters whole groups, typical for thresholds like “batches with at least five failed wells.” Push every predicate you can into WHERE first so the engine groups fewer rows.
WHERE vs HAVING
SELECT primer_batch, COUNT(*) AS wells_failed
FROM qc_wells
WHERE read_date = DATE '2026-04-10'
GROUP BY primer_batch
HAVING COUNT(*) >= 5
ORDER BY wells_failed DESC;| primer_batch | wells_failed |
|---|---|
| PB-77 | 22 |
| PB-12 | 9 |
⚖️Clause comparison
Same query, different evaluation stage.
| Clause | Filters | Aggregates in predicate? |
|---|---|---|
| WHERE | Rows before GROUP BY | No (unless subquery) |
| HAVING | Groups after aggregation | Yes |
HAVING read_date = …
The engine may still compute aggregates for groups you will discard, wasting CPU and memory on large fact tables.
Move row filters to WHERE
Put raw column predicates in WHERE; reserve HAVING for COUNT(*), SUM(…), and similar group-level logic.
Aliases and dialects
Some engines allow HAVING to reference SELECT aliases; others do not, repeat the aggregate expression or wrap in an outer query for portable SQL. Window predicates use QUALIFY on engines that support it, not the same as HAVING.
TL;DR
HAVING filters groups after GROUP BY: use it for predicates on aggregates (HAVING COUNT(*) > 5). WHERE filters rows before grouping and cannot reference aggregates in standard SQL. For filtering on window results, use an outer query or engine extensions like QUALIFY (BigQuery/Snowflake): not HAVING.