PostgreSQL HAVING Clause

PostgreSQL HAVING Clause

The HAVING clause in PostgreSQL is used to filter grouped results after the GROUP BY clause.
While WHERE filters rows, HAVING filters groups.

1️⃣ Why HAVING?

You cannot use aggregate functions (COUNT, SUM, AVG, etc.) in WHERE.
That’s where HAVING is needed.

2️⃣ Basic HAVING Syntax

SELECT column_name, aggregate_function(column)
FROM table_name
GROUP BY column_name
HAVING condition;

3️⃣ Simple HAVING Example

SELECT user_id, COUNT(*) AS total_orders
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 2;

✔ Shows only users with more than 2 orders.

4️⃣ HAVING with SUM

SELECT user_id, SUM(amount) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(amount) >= 1000;

5️⃣ WHERE vs HAVING (Very Important)

SELECT user_id, SUM(amount) AS total_spent
FROM orders
WHERE status = 'completed'
GROUP BY user_id
HAVING SUM(amount) > 1000;

Execution order:

  1. FROM

  2. WHERE → filters rows

  3. GROUP BY

  4. HAVING → filters groups

  5. SELECT

  6. ORDER BY

6️⃣ HAVING with Multiple Conditions

SELECT user_id, COUNT(*) AS orders_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 1 AND SUM(amount) > 500;

7️⃣ HAVING without GROUP BY

Allowed when using aggregate on entire table:

SELECT COUNT(*) AS total_orders
FROM orders
HAVING COUNT(*) > 100;

✔ Works on single group (whole table).

8️⃣ HAVING with JOIN

SELECT u.name, COUNT(o.id) AS total_orders
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.name
HAVING COUNT(o.id) >= 3;

9️⃣ Common HAVING Conditions

ConditionExample
CountHAVING COUNT(*) > 5
SumHAVING SUM(amount) > 1000
AvgHAVING AVG(amount) > 300
MaxHAVING MAX(amount) > 500

🔟 Common Errors & Fixes

❌ Using aggregate in WHERE
✔ Move condition to HAVING

❌ Missing GROUP BY column
✔ Add non-aggregated columns to GROUP BY

1️⃣1️⃣ Best Practices

✔ Use WHERE before GROUP BY whenever possible
✔ Use HAVING only for aggregate conditions
✔ Keep HAVING logic simple
✔ Index columns used in WHERE and GROUP BY

Quick Summary

WHEREHAVING
Filters rowsFilters groups
Before GROUP BYAfter GROUP BY
No aggregatesAggregates allowed

You may also like...