Lesson 13 of 26 · see the practice database

Filtering groups with HAVING

WHERE filters rows. But after grouping, you often want to filter the groups: only the categories with more than three products, only the customers with at least two orders. WHERE cannot do it, because it runs before the groups exist. HAVING can.

The problem

Which categories have an average price above 400 rupees? This is the natural first try.

SELECT category, AVG(price)
FROM products
WHERE AVG(price) > 400
GROUP BY category;

Error: misuse of aggregate: AVG()

The database refuses. At the time WHERE runs, it is looking at single rows. There is no average yet.

HAVING

HAVING goes after GROUP BY. It is a WHERE for groups, and it can use aggregate functions.

SELECT category, ROUND(AVG(price)) AS average
FROM products
GROUP BY category
HAVING AVG(price) > 400;

The database forms the four groups, works out the average of each, and then keeps only the groups that pass.

Counting and keeping

Which customers have placed more than two orders?

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

Which cities have more than one customer?

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

Finding duplicates

HAVING COUNT(*) > 1 is the standard way to find values that appear more than once. It is one of the most useful patterns in the language for checking data. Are any two employees on exactly the same salary?

SELECT salary, COUNT(*) AS people
FROM employees
GROUP BY salary
HAVING COUNT(*) > 1;

In real work the same query finds customers entered twice with the same email address, or invoice numbers used twice.

WHERE and HAVING together

A query can have both. Each does its own job at its own time.

• WHERE removes rows before grouping.
• HAVING removes groups after grouping.

Among delivered orders only, which customers have at least two?

SELECT customer_id, COUNT(*) AS delivered
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY delivered DESC;

Read it in the order it runs. Take the orders. Keep the delivered ones. Pile them by customer. Count each pile. Keep the piles of two or more. Sort.

Which one should hold a condition?

Ask one question: is the test about a single row, or about a group as a whole?

A test on a plain column may be legal in HAVING if you grouped by that column, and it returns the same result. Put it in WHERE anyway. Filtering early means fewer rows to group, which is faster, and it keeps each clause to its proper job.

SELECT category, COUNT(*) AS products
FROM products
WHERE category <> 'Home'
GROUP BY category;

Several conditions

HAVING takes AND and OR like WHERE.

SELECT category, COUNT(*) AS products, SUM(stock) AS stock
FROM products
GROUP BY category
HAVING COUNT(*) = 4 AND SUM(stock) > 150;

The aggregate in HAVING need not be shown

You can filter on a figure that is not in the SELECT list.

SELECT department
FROM employees
GROUP BY department
HAVING MAX(salary) < 50000;

Departments in which nobody earns 50,000 or more.

Can HAVING use an alias?

In standard SQL, no: write the aggregate again, as the examples here do. MySQL and SQLite allow HAVING orders > 2 with an alias. PostgreSQL, SQL Server and Oracle do not. Repeating the function works everywhere.

HAVING without GROUP BY

It is allowed. The whole table is then one group. It is rarely useful, and seeing it usually means that WHERE was intended.

The full skeleton

You have now met every clause of a query on one table. They are always written in this order:

SELECT   columns and aggregates
FROM     table
WHERE    conditions on rows
GROUP BY columns
HAVING   conditions on groups
ORDER BY columns
LIMIT    number;

A real example with all seven:

SELECT product_id, SUM(quantity) AS units
FROM order_items
WHERE quantity < 10
GROUP BY product_id
HAVING SUM(quantity) >= 4
ORDER BY units DESC
LIMIT 5;

Try it yourself

SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 1;

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

SELECT order_id, COUNT(*) FROM order_items GROUP BY order_id HAVING COUNT(*) >= 3;

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.