HAVING

GROUP BY gives you a total for each group. Very often you then want only some of those groups: customers with more than one order, products that sold more than ₹10,000, departments with at least three people. You cannot do this with WHERE, because WHERE runs before the totals exist.

The HAVING clause filters groups after they have been formed. In this lesson you will use it, compare it carefully with WHERE, and use both in one query.

Filtering groups with HAVING

HAVING comes straight after GROUP BY. Its condition is checked once for each group, and only the groups where it is true are kept.

SELECT customer_id, COUNT(*) AS orders_placed
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY customer_id;

customer_id | orders_placed
------------+--------------
1           | 3
2           | 2
3           | 2
(3 rows)

Eleven customers have placed orders, but only three of them came back for a second one. A shop could use exactly this query to find repeat customers for a loyalty offer.

The condition can use any aggregate function, and it does not have to be one that appears in the SELECT list. Here are the products whose total sales are above ₹10,000:

SELECT product_id, SUM(quantity * unit_price) AS sales
FROM order_items
GROUP BY product_id
HAVING SUM(quantity * unit_price) > 10000
ORDER BY sales DESC;

product_id | sales
-----------+------
3          | 47997
6          | 17998
9          | 12998
(3 rows)

WHERE and HAVING compared

Both clauses filter, but they work at different moments and on different things:

Remember the order MySQL follows: FROM, then WHERE (throw away rows), then GROUP BY (form groups), then HAVING (throw away groups), then SELECT, ORDER BY and LIMIT.

Using WHERE and HAVING together

Many real questions need both. "Which categories have more than 100 units in stock, counting only products under ₹5,000?" has a row condition (price) and a group condition (total stock):

SELECT category, SUM(stock) AS units
FROM products
WHERE price < 5000
GROUP BY category
HAVING SUM(stock) > 100
ORDER BY category;

category    | units
------------+------
Accessories | 312
Storage     | 200
(2 rows)

Follow it step by step. WHERE removes the three products priced at ₹5,000 or more. The remaining nine are grouped by category and their stock is added up. HAVING then keeps only the groups above 100 units: Accessories with 312 and Storage with 200 (the pen drive alone, because the SSD was removed by WHERE).

Changing where a condition goes can change the answer completely. If you moved price < 5000 out of WHERE, the SSD's 30 units would be counted in Storage.

HAVING with several aggregates

A HAVING condition can combine aggregates with AND and OR, just like WHERE. This shows departments that have at least three staff:

SELECT department, COUNT(*) AS staff, ROUND(AVG(salary)) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) >= 3
ORDER BY department;

department  | staff | avg_salary
------------+-------+-----------
Engineering | 4     | 111250
Sales       | 3     | 65667
(2 rows)

You could extend it with AND AVG(salary) > 100000 to find only the large, well-paid departments, which would leave just Engineering.

Aliases in HAVING

MySQL lets you use a column alias in HAVING, so HAVING orders_placed > 1 works on a MySQL server, and in the browser editor too. Standard SQL does not allow it, and databases such as PostgreSQL reject it. Repeating the expression, HAVING COUNT(*) > 1, works everywhere, which is why the examples here use that form.

Put row conditions in WHERE

MySQL will accept a condition on a grouped column in either place:

SELECT category, COUNT(*) FROM products GROUP BY category HAVING category = 'Audio';
SELECT category, COUNT(*) FROM products WHERE category = 'Audio' GROUP BY category;

Both return Audio | 2, but the second is better. With HAVING, MySQL groups all twelve products and then throws four groups away. With WHERE, it ignores the other products from the start, which on a large table means far less work. The rule is simple: conditions on columns go in WHERE, conditions on aggregates go in HAVING.

Common mistakes

• Putting an aggregate in WHERE. WHERE COUNT(*) > 1 gives ERROR 1111 (HY000): Invalid use of group function. Move the condition to HAVING.
• Writing HAVING before GROUP BY. The clause order is fixed: WHERE, GROUP BY, HAVING, ORDER BY.
• Using HAVING for row filters. It usually works but wastes effort, and it can change the result if the column is not the one you grouped by. Use WHERE for anything that is not an aggregate.
• Forgetting that WHERE changes the totals. Rows removed by WHERE are not counted at all, so HAVING sees smaller totals. Decide which rows belong in the count before you write the query.

Key points

• HAVING filters groups after GROUP BY; WHERE filters rows before it.
• Only HAVING can test aggregate values such as COUNT(*) or SUM(...).
• A query can use both: WHERE decides which rows are counted, HAVING decides which totals are shown.
• MySQL allows aliases in HAVING, but repeating the expression is more portable.
• Conditions on plain columns belong in WHERE, where they are faster.

SELECT state, COUNT(*) AS customers
FROM customers
GROUP BY state
HAVING COUNT(*) >= 2
ORDER BY state;

SELECT state, COUNT(*) AS customers
FROM customers
WHERE joined_on < '2025-01-01'
GROUP BY state
HAVING COUNT(*) >= 2
ORDER BY state;