HAVING

The HAVING clause in PostgreSQL is used to filter groups of rows after they have been aggregated by GROUP BY. It is the aggregate equivalent of WHERE — while WHERE filters individual rows before grouping, HAVING filters groups after aggregation.

HAVING clause syntax

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;

PostgreSQL evaluates clauses in this order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Because HAVING is evaluated before SELECT, you cannot reference column aliases defined in SELECT inside a HAVING clause.

HAVING with SUM

Find customers whose total payments exceed $200:

SELECT customer_id, SUM(amount) AS amount
FROM payment
GROUP BY customer_id
HAVING SUM(amount) > 200
ORDER BY amount DESC;

customer_id | amount
-------------+--------
        148 | 211.55
        526 | 208.58
(2 rows)

Without the HAVING clause, the query returns the total for every customer. With it, only groups where the total exceeds 200 appear in the result.

HAVING with COUNT

Find stores with more than 300 customers:

SELECT store_id, COUNT(customer_id)
FROM customer
GROUP BY store_id
HAVING COUNT(customer_id) > 300;

store_id | count
----------+-------
        1 |   326
(1 row)

HAVING vs. WHERE

The distinction between WHERE and HAVING is critical:

• WHERE filters individual rows before they are grouped. It cannot reference aggregate functions.
• HAVING filters groups after aggregation. It can reference aggregate functions like SUM(), COUNT(), AVG().

Use both together when you need to pre-filter rows AND filter the resulting groups:

SELECT customer_id, SUM(amount) AS total
FROM payment
WHERE payment_date >= '2007-01-01'
GROUP BY customer_id
HAVING SUM(amount) > 100
ORDER BY total DESC;

Here, WHERE limits which payment rows are included in the aggregation, and HAVING filters the resulting customer totals.

Practical tips for HAVING

• Cannot use SELECT aliases: because HAVING is evaluated before SELECT, you cannot write HAVING total > 200 where total is a SELECT alias. Repeat the aggregate expression: HAVING SUM(amount) > 200.
• HAVING without GROUP BY: HAVING can be used without GROUP BY, treating the entire table as one group. This is rarely useful but valid — HAVING COUNT(*) > 0 returns results only if the table is non-empty.
• Filter early with WHERE: always filter rows with WHERE before grouping when possible. This reduces the number of rows that need to be aggregated, improving query performance.
• Index considerations: HAVING conditions on aggregates cannot use standard indexes directly. Focus indexing on the GROUP BY columns and any WHERE clause columns.

Reference: PostgreSQL documentation — SELECT.