HAVING Clause in PostgreSQL

The HAVING clause filters groups produced by GROUP BY based on a condition. Unlike WHERE — which filters individual rows before grouping — HAVING is evaluated after aggregation and can reference aggregate functions directly.

Syntax

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE row_condition
GROUP BY column1
HAVING group_condition
ORDER BY column1;

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

Practical Example

Set up a regional sales table:

CREATE TABLE regional_sales (
  sale_id    SERIAL PRIMARY KEY,
  region     VARCHAR NOT NULL,
  rep_id     INT     NOT NULL,
  amount     NUMERIC(10, 2) NOT NULL,
  sale_date  DATE    NOT NULL
);

INSERT INTO regional_sales (region, rep_id, amount, sale_date) VALUES
  ('North', 101, 320.00, '2025-01-10'),
  ('North', 102,  85.00, '2025-01-12'),
  ('South', 103, 410.00, '2025-01-15'),
  ('East',  104,  50.00, '2025-01-20'),
  ('North', 101, 190.00, '2025-02-03'),
  ('South', 103, 230.00, '2025-02-07'),
  ('East',  104, 175.00, '2025-02-14'),
  ('West',  105,  30.00, '2025-02-18');

Find regions where total sales exceed $400:

SELECT region, SUM(amount) AS total_sales
FROM regional_sales
GROUP BY region
HAVING SUM(amount) > 400
ORDER BY total_sales DESC;

Expected result:

 region | total_sales
--------+-------------
 North  |      595.00
 South  |      640.00

Combine WHERE and HAVING — filter to Q1 rows first, then keep only regions with more than one sale:

SELECT
  region,
  COUNT(*)        AS sale_count,
  SUM(amount)     AS total_sales,
  AVG(amount)     AS avg_sale
FROM regional_sales
WHERE sale_date BETWEEN '2025-01-01' AND '2025-03-31'
GROUP BY region
HAVING COUNT(*) > 1
ORDER BY total_sales DESC;

The WHERE clause reduces the dataset before grouping; HAVING filters the resulting groups. This is more efficient than a single HAVING because fewer rows are processed by the aggregation engine.

Verify which regions are excluded by inspecting ungrouped data:

SELECT region, SUM(amount) AS total
FROM regional_sales
GROUP BY region
ORDER BY total;

WHERE vs HAVING Comparison

Testing with Vela

Use Vela’s database branching to validate HAVING-based reporting queries against a production-data clone before deploying them. Create a branch, run EXPLAIN ANALYZE on the query, and confirm the planner is using the expected indexes on the WHERE filter columns. This is particularly useful when tuning the boundary between WHERE (pre-aggregation) and HAVING (post-aggregation) conditions.

Production Tips

• Always use WHERE for non-aggregate conditions and reserve HAVING for aggregate conditions. WHERE can leverage indexes; HAVING cannot.
• Avoid placing non-aggregate column filters in HAVING — they force the engine to aggregate all rows before filtering, rather than filtering first.
• When HAVING is used without GROUP BY, the entire table is treated as one group. This is rarely useful but valid — HAVING COUNT(*) > 0 returns results only if the table is non-empty.
• For dashboards that run the same HAVING query repeatedly, a pre-aggregated materialized view with a refresh schedule can dramatically reduce query time.
• Combine HAVING with GROUPING() when filtering groups from GROUPING SETS, ROLLUP, or CUBE outputs — for example, HAVING GROUPING(region) = 0 limits results to rows that are genuinely grouped by region rather than subtotal rows.