SQL HAVING Clause

HAVING filters groups after GROUP BY has calculated them. Use it whenever the condition depends on an aggregate, such as "customers with more than 2 orders" or "products that sold over ₹20,000".

Syntax

SELECT group_column, AGGREGATE(col)
FROM table_name
WHERE row_condition        -- filters rows
GROUP BY group_column
HAVING group_condition     -- filters groups
ORDER BY ...;

WHERE vs HAVING

Examples

1. Repeat customers

Customers who placed 3 or more orders:

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

Result:

(3 rows)

2. Best-selling products by revenue

Products with more than ₹20,000 in net revenue:

SELECT product_id,
       ROUND(SUM(quantity * unit_price * (1 - discount_pct / 100)), 2) AS net_revenue
FROM order_items
GROUP BY product_id
HAVING SUM(quantity * unit_price * (1 - discount_pct / 100)) > 20000
ORDER BY net_revenue DESC;

Result:

(3 rows)

Notice the full expression is repeated in HAVING. PostgreSQL won't accept the alias net_revenue there.

3. WHERE and HAVING together

Customers with at least 2 delivered orders:

SELECT customer_id,
       COUNT(*) AS delivered_orders
FROM orders
WHERE status = 'Delivered'       -- step 1: keep delivered rows
GROUP BY customer_id             -- step 2: group by customer
HAVING COUNT(*) >= 2             -- step 3: keep groups with 2+
ORDER BY delivered_orders DESC, customer_id;

Result:

(4 rows)

Compare with the same query without WHERE: customers would qualify based on cancelled and pending orders too.

4. Groups with an average condition

Categories whose average product price is over ₹3,000:

SELECT category_id,
       COUNT(*) AS products,
       ROUND(AVG(price), 0) AS avg_price
FROM products
GROUP BY category_id
HAVING AVG(price) > 3000
ORDER BY avg_price DESC;

Result:

(2 rows)

5. Finding duplicates

HAVING COUNT(*) > 1 is the standard way to find duplicate values, a very common interview question:

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
HAVING COUNT(*) > 1;

Result:

(1 row)

Common mistakes

Putting an aggregate in WHERE:

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

Error: aggregate functions are not allowed in WHERE

• Using HAVING for row filters. HAVING status = 'Delivered' (with status grouped) works but is slower and confusing. Row conditions belong in WHERE.
• Using the alias in HAVING in PostgreSQL. Repeat the expression.

Practice question

Which payment methods were used for more than 5 orders? Show the method and the number of orders.

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

Key takeaways

• HAVING filters groups; WHERE filters rows.
• Conditions on COUNT, SUM, AVG etc. go in HAVING.
• Query order: WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
• GROUP BY ... HAVING COUNT(*) > 1 finds duplicates.