MySQL HAVING Clause

Last updated: October 19, 2026Oct 19, 2026 4 min read

HAVING filters the groups produced by GROUP BY, using aggregate values: customers who ordered more than twice, cities with revenue over ₹2,000, emails that appear more than once. WHERE can't do this, because it runs before groups exist.

1. WHERE vs HAVING

Table: orders (10 rows)

2. Filtering Groups

Customers with more than two orders:

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

Result: 2 rows

Trying the same with WHERE fails:

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

ERROR 1111 (HY000): Invalid use of group function

3. Using WHERE and HAVING Together

Count only paid orders (WHERE), then keep cities whose paid revenue exceeds ₹2,000 (HAVING):

SELECT city, COUNT(*) AS paid_orders, SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY city
HAVING SUM(amount) > 2000
ORDER BY revenue DESC;

Result: 2 rows

Without the WHERE, Chetna's cancelled ₹3,000 order would wrongly count towards Pune.

4. Several Conditions and Aliases

SELECT customer, COUNT(*) AS orders, ROUND(AVG(amount)) AS avg_order
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING orders >= 2 AND avg_order < 1000;

Result: 2 rows

5. Finding Duplicates

A classic use: emails that appear more than once, and how many times:

SELECT email, COUNT(*) AS copies, GROUP_CONCAT(id ORDER BY id) AS ids
FROM subscribers
GROUP BY email
HAVING COUNT(*) > 1;

Result: 2 rows

6. HAVING with MIN, MAX and Dates

Customers whose first order was in January and who are still ordering in February or later:

SELECT customer, MIN(ordered_on) AS first_order, MAX(ordered_on) AS last_order
FROM orders
GROUP BY customer
HAVING MIN(ordered_on) < '2026-02-01' AND MAX(ordered_on) >= '2026-02-01';

Result: 3 rows

7. HAVING Without GROUP BY

Without GROUP BY the whole table is one group. This returns a row only if the condition holds:

SELECT COUNT(*) AS pending FROM orders WHERE status = 'pending' HAVING COUNT(*) > 0;

Result: 1 row

8. Put Row Filters in WHERE

A condition on a plain column (not an aggregate) works in HAVING too, but it is applied after all the grouping work. Put it in WHERE so rows are discarded early, ideally using an index:

SELECT city, SUM(amount) FROM orders GROUP BY city HAVING city <> 'Kochi';

Works, but filters late

SELECT city, SUM(amount) FROM orders WHERE city <> 'Kochi' GROUP BY city;

Better: filters early

9. Practice Exercises

SELECT DATE_FORMAT(ordered_on, '%Y-%m') AS month, COUNT(*) AS orders
FROM orders
GROUP BY month
HAVING COUNT(*) >= 4;

SELECT customer, MAX(amount) AS biggest
FROM orders WHERE status = 'paid'
GROUP BY customer
HAVING MAX(amount) < 1000;

10. Summary

• HAVING filters groups after GROUP BY; WHERE filters rows before.
• Only HAVING can use aggregates such as COUNT(*) and SUM().
• Combine both: WHERE to choose rows, HAVING to choose groups.
• HAVING COUNT(*) > 1 finds duplicates.
• Keep non-aggregate conditions in WHERE for speed.