PostgreSQL Having

Why HAVING Exists

WHERE filters individual rows before they are grouped. By the time GROUP BY has produced its summary rows, WHERE has already finished its job and the aggregate values (like SUM or COUNT) don't exist yet for it to reference. HAVING solves this by running its filter after the aggregation step, so it can test conditions against the aggregate results themselves.

A useful mental model: WHERE decides which raw rows enter the grouping process; HAVING decides which finished groups survive into the final result.

Basic Syntax

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5
ORDER BY order_count DESC;

This query would be impossible to write correctly with WHERE COUNT(*) > 5, because COUNT(*) has no meaning until rows have already been grouped together.

Combining WHERE and HAVING

WHERE and HAVING are not mutually exclusive — most non-trivial reports use both. WHERE trims the input rows early (cheaper, since it avoids aggregating irrelevant data), and HAVING trims the output groups afterward.

SELECT region, COUNT(*) AS customer_count, SUM(lifetime_value) AS region_value
FROM customers
WHERE signup_date >= '2024-01-01'
GROUP BY region
HAVING SUM(lifetime_value) > 100000
ORDER BY region_value DESC;

Here WHERE restricts the dataset to customers who signed up in 2024 or later, GROUP BY buckets them by region, and HAVING keeps only regions whose combined lifetime value exceeds 100,000.

HAVING Without an Explicit Aggregate Reference

SELECT department, COUNT(DISTINCT job_title) AS distinct_titles
FROM employees
GROUP BY department
HAVING COUNT(DISTINCT job_title) = 1;

• WHERE filters rows before grouping; HAVING filters groups after grouping
• HAVING conditions typically reference an aggregate function directly
• You can use HAVING even without GROUP BY, treating the whole table as one group
• Non-aggregated columns in HAVING must still appear in GROUP BY, same rule as SELECT
• Query execution order is: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY