SQL WHERE vs HAVING: Filter Rows or Groups? — sqlfmt

sqlfmt▍

SQL WHERE vs HAVING: Filter Rows or Groups? — sqlfmt

Key takeaways

WHERE filters source rows.
HAVING filters aggregate groups.
A report can use both clauses correctly.

What is the difference?

WHERE answers which rows should enter a calculation. HAVING answers which completed groups should appear. A COUNT-based condition belongs in HAVING because it exists only after grouping.

Filter paid rows, then keep high-value customers

SELECT customer_id, COUNT(*) AS paid_orders, SUM(total_amount) AS paid_revenue
FROM orders
WHERE status = 'paid'
  AND ordered_at >= DATE '2026-01-01'
GROUP BY customer_id
HAVING SUM(total_amount) >= 1000;

PostgreSQL documents that WHERE is evaluated before grouping, while HAVING removes groups that do not meet its condition.

Why not put every condition in HAVING?

A row-level predicate is clearest in WHERE. Keeping a predicate at the level it describes makes a query easier to audit and avoids dialect-specific behavior.

A practical decision rule

Use WHERE when one source row can answer the condition.
Use HAVING when the condition needs COUNT, SUM, AVG, MIN, or MAX.
Use both when input filtering and group thresholds are both required.

For a PostgreSQL grouping diagnostic, see the GROUP BY clause error guide.

Frequently asked questions

Can HAVING work without GROUP BY?

Many databases treat an aggregate query without GROUP BY as one implicit group. Verify the exact behavior in the documentation for your database.

Can WHERE use COUNT(*)?

Not at the same query level. Use HAVING, or calculate the aggregate in a subquery or CTE and filter its output.