How does the having: filter groups query work?

WHERE runs first and removes orders outside the last 12 months before any grouping happens. GROUP BY then builds one row per customer with its count and total. HAVING runs last and removes customers whose aggregate values do not meet the condition.

What commonly goes wrong with having: filter groups in SQL?

A condition on a raw column belongs in WHERE: it is faster and clearer than putting it in HAVING.

HAVING: Filter Groups in SQL

HAVING filters groups after they have been aggregated, such as customers with at least three orders, while WHERE filters rows before.

Example query

This example assumes: orders(id, customer_id bigint, order_date date, total_amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT customer_id,
       COUNT(*) AS orders,
       SUM(total_amount) AS spend
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY customer_id
HAVING COUNT(*) >= 3
   AND SUM(total_amount) > 500
ORDER BY spend DESC;

How it works

WHERE runs first and removes orders outside the last 12 months before any grouping happens.
GROUP BY then builds one row per customer with its count and total.
HAVING runs last and removes customers whose aggregate values do not meet the condition.

MySQL, BigQuery, SQL Server and SQLite differences

HAVING works the same in every major database. MySQL lets you refer to an output alias in HAVING; PostgreSQL and SQL Server do not, so repeat the expression there.

Common mistakes

A condition on a raw column belongs in WHERE: it is faster and clearer than putting it in HAVING.
Filtering a group by a count, then joining back to the rows, is a common way to double count; aggregate first, join second.
HAVING COUNT(*) = 0 never returns anything, since groups with no rows do not exist; use an anti-join for 'none'.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

Which customers placed at least 3 orders in the last year?
Which products had more than 100 returns this quarter?