Skip to main content

HAVING Clause

Learning Focus

Use this lesson to understand HAVING Clause with practical syntax and examples.

Concept Overview​

HAVING filters groups after aggregation. If WHERE is “filter raw rows”, then HAVING is “filter the aggregated output”.

Why is it important?​

  • Correctness: aggregate conditions (like “customers with >= 5 orders”) must be applied after grouping
  • Performance: keeping row filters in WHERE reduces work before grouping
  • Clarity: separating row filters (WHERE) from group filters (HAVING) makes queries easier to review

Where does it fit?​

HAVING is used with GROUP BY and aggregate functions. The typical query flow is:

  1. WHERE filters rows
  2. GROUP BY forms groups
  3. aggregates compute metrics per group
  4. HAVING filters groups
  5. ORDER BY sorts final groups

Syntax & Rules​

Core Syntax​

SELECT group_key,
       aggregate_fn(expr) AS metric
FROM table_name
WHERE row_filters
GROUP BY group_key
HAVING aggregate_predicate
ORDER BY metric DESC;

Available Options / Parameters​

ClauseEvaluated onTypical use
WHEREraw rows (before grouping)WHERE status = 'paid'
HAVINGgrouped rows (after aggregates computed)HAVING COUNT(*) >= 5

Key Rules and Considerations​

  • Use WHERE for non-aggregate filters (status, date windows, region filters).
  • Use HAVING for conditions on aggregates (COUNT/SUM/AVG).
  • In PostgreSQL, you generally cannot reference a SELECT alias inside HAVING. Repeat the expression or use a subquery/CTE.
  • You can have a HAVING clause without GROUP BY (it filters the single aggregated result), but it is much more common with grouping.

Step-by-Step Examples​

Example 1: Filter Groups by COUNT(*) (Beginner)​

CREATE TABLE orders (
  order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  status      text NOT NULL,
  amount      numeric(12,2) NOT NULL CHECK (amount >= 0)
);

INSERT INTO orders (customer_id, status, amount) VALUES
  (10, 'paid',  25.00),
  (10, 'paid',  10.00),
  (10, 'failed', 5.00),
  (11, 'paid',  50.00),
  (11, 'paid',  20.00),
  (12, 'paid',  5.00);

-- Customers with at least 2 orders
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY customer_id;

Expected output:

 customer_id | order_count
-------------+------------
 10          | 3
 11          | 2
(2 rows)

Explanation:

  • HAVING COUNT(*) >= 2 is evaluated after the groups are formed.

Example 2: Keep Row Filters in WHERE, Group Filters in HAVING (Intermediate)​

Goal: customers with at least 2 paid orders and paid revenue >= 40.

SELECT
  customer_id,
  COUNT(*) AS paid_orders,
  SUM(amount) AS paid_revenue
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) >= 2
   AND SUM(amount) >= 40
ORDER BY paid_revenue DESC;

Expected output:

 customer_id | paid_orders | paid_revenue
-------------+------------+-------------
 11          | 2          | 70.00
(1 row)

Explanation:

  • The WHERE status = 'paid' filter reduces the rows that participate in the aggregates.
  • HAVING filters the grouped results.

Example 3: Conditional Aggregates with FILTER + HAVING (Advanced)​

This pattern keeps all rows, computes multiple metrics, then filters groups using HAVING.

SELECT
  customer_id,
  COUNT(*) AS total_orders,
  COUNT(*) FILTER (WHERE status = 'paid') AS paid_orders,
  SUM(amount) FILTER (WHERE status = 'paid') AS revenue_paid
FROM orders
GROUP BY customer_id
HAVING COUNT(*) FILTER (WHERE status = 'paid') >= 2
ORDER BY customer_id;

Expected output:

 customer_id | total_orders | paid_orders | revenue_paid
-------------+-------------+------------+-------------
 10          | 3           | 2          | 35.00
 11          | 2           | 2          | 70.00
(2 rows)

Explanation:

  • FILTER allows you to write group conditions that are more precise than “use WHERE and lose other rows”.

Example 4: Alias Scope in PostgreSQL (Advanced)​

In PostgreSQL, aliases from the SELECT list are not reliably visible in HAVING. Use the aggregate expression or a CTE.

Wrong SQL:

SELECT customer_id, SUM(amount) AS total_spend
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING total_spend >= 40;

Typical error:

ERROR:  column "total_spend" does not exist

Fix option A (repeat the expression):

SELECT customer_id, SUM(amount) AS total_spend
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) >= 40;

Fix option B (use a CTE):

WITH totals AS (
  SELECT customer_id, SUM(amount) AS total_spend
  FROM orders
  WHERE status = 'paid'
  GROUP BY customer_id
)
SELECT customer_id, total_spend
FROM totals
WHERE total_spend >= 40
ORDER BY total_spend DESC;

Practical Use Cases​

1) High-value customers​

SELECT customer_id, SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) >= 10000;

2) Products with low review counts​

SELECT product_id, COUNT(*) AS review_count
FROM reviews
GROUP BY product_id
HAVING COUNT(*) < 5;

3) Detect duplicate keys in raw data​

SELECT external_id, COUNT(*) AS copies
FROM staging_events
GROUP BY external_id
HAVING COUNT(*) > 1;

4) Identify teams with too many open incidents​

SELECT team_id, COUNT(*) AS open_incidents
FROM incidents
WHERE status = 'open'
GROUP BY team_id
HAVING COUNT(*) >= 10;

5) Filter groups by average​

SELECT course_id, AVG(score) AS avg_score
FROM exam_results
GROUP BY course_id
HAVING AVG(score) BETWEEN 70 AND 90;

Common Mistakes & Troubleshooting​

1) Putting aggregate predicates in WHERE​

Wrong SQL:

SELECT customer_id, COUNT(*)
FROM orders
WHERE COUNT(*) >= 2
GROUP BY customer_id;

Typical error:

ERROR:  aggregate functions are not allowed in WHERE

Fix:

SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2;

2) Using HAVING for row filters only​

Wrong approach:

SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id
HAVING status = 'paid';

Bad outcome:

  • status is not aggregated or grouped, so this is invalid (or indicates confused intent).

Fix:

SELECT customer_id, COUNT(*)
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;

3) Forgetting that WHERE changes the population​

Bad outcome:

  • Filtering rows before grouping changes what “COUNT(*)” means.

Fix:

  • Use FILTER if you need both totals and conditional totals in the same grouped output.

4) Assuming SELECT aliases work in HAVING​

Bad outcome:

  • PostgreSQL often rejects alias references.

Fix:

  • Repeat the expression or wrap the grouped query and filter in an outer SELECT.

Debugging checklist:

  1. If a predicate depends on an aggregate (COUNT/SUM/AVG), it belongs in HAVING.
  2. If a predicate depends on raw row columns, it belongs in WHERE.
  3. If you need both “total” and “paid-only” metrics, consider FILTER.
  4. If you see alias scope errors, use a CTE/subquery.
  5. Verify results by comparing a few groups with manual counts.

Best Practices​

  • ✅ Put row filters in WHERE. ❌ Avoid using HAVING as a replacement for WHERE.
  • ✅ Put aggregate filters in HAVING. ❌ Avoid aggregate expressions in WHERE.
  • ✅ Use FILTER for conditional group metrics. ❌ Avoid complicated SUM(CASE WHEN ... THEN ... END) unless you need portability.
  • ✅ Alias aggregates for readability. ❌ Avoid repeating long expressions without aliases or CTEs.
  • ✅ Use CTEs to reuse computed aggregates in later filtering. ❌ Avoid relying on SELECT alias visibility in PostgreSQL HAVING.

Hands-On Practice​

Use this setup for the exercises:

CREATE TABLE practice_orders (
  customer_id bigint NOT NULL,
  status      text NOT NULL,
  amount      numeric(12,2) NOT NULL
);

INSERT INTO practice_orders (customer_id, status, amount) VALUES
  (1, 'paid', 10.00),
  (1, 'paid', 15.00),
  (2, 'paid', 5.00),
  (2, 'failed', 20.00),
  (3, 'paid', 50.00);

Exercise 1 (Easy): Customers with 2+ orders​

Task: Return customers that have at least 2 orders.

-- Your SQL here

Solution:

SELECT customer_id, COUNT(*) AS orders
FROM practice_orders
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY customer_id;

Exercise 2 (Medium): Paid-only customers with 2+ paid orders​

Task: Return customers that have at least 2 paid orders.

-- Your SQL here

Solution:

SELECT customer_id, COUNT(*) AS paid_orders
FROM practice_orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY customer_id;

Exercise 3 (Advanced): Conditional paid count with FILTER​

Task: Return customers where paid_orders >= 2 using FILTER.

-- Your SQL here

Solution:

SELECT
  customer_id,
  COUNT(*) AS total_orders,
  COUNT(*) FILTER (WHERE status = 'paid') AS paid_orders
FROM practice_orders
GROUP BY customer_id
HAVING COUNT(*) FILTER (WHERE status = 'paid') >= 2
ORDER BY customer_id;

Connection to Other Concepts​

ConceptWhy it matters
GROUP BYHAVING filters the output of grouped aggregates
WHEREfilters input rows before aggregation
Aggregate functionsHAVING predicates use COUNT/SUM/AVG/etc
NULL Valuesaffects SUM/AVG behavior and conditional aggregates
ROLLUP / GROUPING SETSgroup-level outputs often need HAVING-like filters

Visual Learning Diagram​

Common Pitfalls​

PitfallConsequencePrevention
Aggregate predicate in WHEREquery errormove it to HAVING
Row predicate in HAVINGslow/invalid intentput it in WHERE
Using SELECT alias in HAVINGscope errorrepeat expression or use a CTE
Filtering before grouping unintentionallywrong populationdecide whether to use WHERE vs FILTER
Not validating group resultsmisleading reportsspot-check a few groups

Quick Reference​

SELECT k, COUNT(*) c FROM t GROUP BY k HAVING COUNT(*) > 10;
SELECT k, SUM(v) s FROM t WHERE status = 'ok' GROUP BY k HAVING SUM(v) >= 1000;
SELECT k, AVG(score) a FROM t GROUP BY k HAVING AVG(score) BETWEEN 70 AND 90;
SELECT k, COUNT(*) FILTER (WHERE ok) FROM t GROUP BY k HAVING COUNT(*) FILTER (WHERE ok) > 5;
WITH x AS (SELECT k, SUM(v) s FROM t GROUP BY k) SELECT * FROM x WHERE s > 100;

What's Next​