Filter groups with HAVING (find duplicates & above-average groups)

HAVING filters groups after aggregation, the way WHERE filters rows before it. The classic use: find keys that appear more than once.

-- Find duplicate emails — identical in all six dialects
SELECT email, COUNT(*) AS n
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY n DESC;

HAVING can compare an aggregate to anything, including another aggregate query — e.g. “products that sold above the average product’s total”:

SELECT product_id, SUM(amount) AS total
FROM sales
GROUP BY product_id
HAVING SUM(amount) > (
  SELECT AVG(product_total)
  FROM (SELECT SUM(amount) AS product_total FROM sales GROUP BY product_id) x
)
ORDER BY total DESC;

WHERE runs before grouping, HAVING runs after. WHERE can’t reference COUNT(*) or any other aggregate — the rows haven’t been grouped yet when it runs. HAVING can, because it evaluates after GROUP BY. For performance, filter what you can in WHERE (fewer rows to aggregate); reserve HAVING for conditions on the aggregate itself. Some dialects (MySQL, SQLite) let HAVING reference a SELECT-list alias as a convenience (HAVING n > 1); PostgreSQL doesn’t. Repeating the aggregate expression — HAVING COUNT(*) > 1, as above — works identically in all six.