Tutorials

SQL HAVING Clause: GROUP BY Filters, Examples, and HAVING vs WHERE

For someone who has just learned GROUP BY and needs to filter the groups; every query below is shown with the rows it returns.

by Malik Umair Sajjad · 08-Oct-2023 · Updated 05-Sep-2026

On this page

A grouped query gives you one row per group, and the report only wants some of them: the groups whose total passes a threshold, or whose row count is high enough to be worth printing. Putting that condition in WHERE fails, because WHERE runs before the groups exist. HAVING is the clause that runs after them, and it can read the aggregates the grouping produced.

What the SQL HAVING clause does

HAVING takes a condition built on an aggregate expression, COUNT(*), SUM(amount), AVG(score), MIN(created_at), MAX(order_total), and keeps only the groups for which it is true. Everything in this article runs against seven orders and three customers:

CREATE TABLE Customers (
    customer_id   INT PRIMARY KEY,
    customer_name VARCHAR(50)
);

CREATE TABLE Orders (
    order_id    INT PRIMARY KEY,
    customer_id INT,
    region      VARCHAR(50),
    order_date  DATE,
    amount      DECIMAL(10, 2),
    status      VARCHAR(20)
);

INSERT INTO Customers VALUES
    (1, 'Ada Lawson'),
    (2, 'Grace Miller'),
    (3, 'Linus Park');

INSERT INTO Orders VALUES
    (1, 1, 'North', '2024-11-20', 300.00, 'Paid'),
    (2, 1, 'North', '2025-02-05', 250.00, 'Paid'),
    (3, 1, 'East',  '2025-03-11', 350.00, 'Pending'),
    (4, 2, 'North', '2025-01-15', 200.00, 'Paid'),
    (5, 2, 'South', '2025-04-02', 100.00, 'Paid'),
    (6, 2, 'South', '2025-05-19', 300.00, 'Paid'),
    (7, 3, 'East',  '2025-06-08', 900.00, 'Paid');

Ada and Grace placed three orders each, Linus placed one. Asking for the customers with at least three of them is a question about groups, so the condition goes in HAVING:

SELECT customer_id, COUNT(*) AS total_orders
FROM Orders
GROUP BY customer_id
HAVING COUNT(*) >= 3;

customer_id	total_orders
1	3
2	3

The database grouped all seven rows first, counted each group, and only then dropped the group with one order. No row was filtered on its way in.

SQL HAVING syntax

The clause sits between GROUP BY and ORDER BY:

SELECT grouped_column, aggregate_function(column_name)
FROM table_name
WHERE row_condition
GROUP BY grouped_column
HAVING aggregate_function(column_name) condition
ORDER BY grouped_column;

That position follows from the order in which the database works through the query:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

WHERE is step 2 and grouping is step 3, so at the time WHERE runs there is no group to count and no total to compare against. HAVING is step 4, one step after the aggregates exist.

HAVING vs WHERE

Clause	Filters	Runs before grouping?	Typical use
`WHERE`	individual rows	yes	keep only this year's orders
`HAVING`	grouped results	no	keep only customers with three orders

Both clauses in one query show the division of labor. WHERE throws away the 2024 order before grouping, and HAVING then judges what is left:

SELECT customer_id, COUNT(*) AS total_orders
FROM Orders
WHERE order_date >= DATE '2025-01-01'
GROUP BY customer_id
HAVING COUNT(*) >= 3;

customer_id	total_orders
2	3

Ada dropped out of a result she was in two queries ago. Her order from November 2024 never reached the grouping, so her count is 2 rather than 3, and the same HAVING condition that kept her before now rejects her. Move a condition between the two clauses and you change the answer, not just the speed. For row-level filtering on its own, see SQL WHERE Clause.

SQL HAVING examples with COUNT, SUM, and AVG

HAVING with COUNT

SELECT region, COUNT(*) AS total_orders
FROM Orders
GROUP BY region
HAVING COUNT(*) > 2;

region	total_orders
North	3

South and East have two orders each, so North is the only region left. A count filter like this one keeps a report from drawing conclusions about a region that sold twice.

HAVING with SUM

SELECT region, SUM(amount) AS total_sales
FROM Orders
GROUP BY region
HAVING SUM(amount) > 500
ORDER BY total_sales DESC;

region	total_sales
East	1250.00
North	750.00

East wins on two orders and North on three, while South adds up to 400.00 and falls out. SUM() in HAVING and SUM() in the select list are the same expression computed once per group.

HAVING with AVG

SELECT customer_id, AVG(amount) AS average_order_value
FROM Orders
GROUP BY customer_id
HAVING AVG(amount) >= 250;

customer_id	average_order_value
1	300.00
3	900.00

Linus, with his single order of 900.00, has the highest average of the three, which is what an average over one row tends to do. Pair the average with COUNT(*) in the select list whenever the number will be read by someone making a decision. For the aggregate functions themselves, read SQL COUNT(), AVG(), and SUM() Functions.

HAVING with multiple conditions

Conditions combine with AND and OR exactly as they do in WHERE:

SELECT region,
       COUNT(*) AS total_orders,
       SUM(amount) AS total_sales
FROM Orders
GROUP BY region
HAVING COUNT(*) >= 2
   AND SUM(amount) > 500;

region	total_orders	total_sales
North	3	750.00
East	2	1250.00

South passes the first condition with two orders and fails the second with 400.00. With OR in place of AND, one condition is enough:

SELECT region, AVG(amount) AS average_order_value
FROM Orders
GROUP BY region
HAVING AVG(amount) > 300
    OR COUNT(*) > 2;

region	average_order_value
North	250.00
East	625.00

North is in the result on its order count alone, since its average of 250.00 is under the threshold. An aggregate can appear in the condition without appearing in the select list, which is why the column that let North through is nowhere in the output.

HAVING with COUNT DISTINCT

COUNT(DISTINCT column) counts the different values in a group rather than the rows, and it works in HAVING like any other aggregate:

SELECT customer_id,
       COUNT(DISTINCT region) AS active_regions
FROM Orders
GROUP BY customer_id
HAVING COUNT(DISTINCT region) >= 2;

customer_id	active_regions
1	2
2	2

Ada ordered twice in North and once in East, and COUNT(DISTINCT region) reports 2 where COUNT(*) would report 3. That difference is the whole point of the form: customers buying across regions, users signing in from several devices, products sold in more than one category.

HAVING with JOINs

The grouping happens after the join, so a HAVING condition sees the joined rows. Customers with at least two paid orders, by name:

SELECT c.customer_name, COUNT(o.order_id) AS paid_orders
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.status = 'Paid'
GROUP BY c.customer_name
HAVING COUNT(o.order_id) >= 2
ORDER BY paid_orders DESC;

customer_name	paid_orders
Grace Miller	3
Ada Lawson	2

Three clauses did three separate jobs here. The join brought the names in, WHERE removed Ada's pending order before any counting, and HAVING dropped Linus for having one paid order. Reversing the last two would count the pending order and give Ada three.

Can you use HAVING without GROUP BY?

A query can carry HAVING with no GROUP BY, and the whole result then becomes one group. The PostgreSQL 17 SELECT documentation puts it plainly: the presence of HAVING turns a query into a grouped query even without GROUP BY, all selected rows are considered to form a single group, and the query emits a single row when the condition is true and no rows when it is false.

SELECT COUNT(*) AS total_orders
FROM Orders
HAVING COUNT(*) > 5;

total_orders
7

Seven orders is more than five, so the single group survives and the count comes back. Raise the threshold to 100 and the query returns nothing at all, rather than a row holding zero, which makes this shape useful for a check that should be silent while everything is fine. In day-to-day reporting, HAVING comes with a GROUP BY.

Aliases, portability, and database tips

An alias from the select list inside HAVING is where engines part company:

SELECT region, SUM(amount) AS total_sales
FROM Orders
GROUP BY region
HAVING total_sales > 500;

MySQL 8.4 runs it. Its SELECT documentation says the SQL standard requires HAVING to reference only columns in GROUP BY or columns used in aggregate functions, and that MySQL supports an extension permitting HAVING to refer to columns in the select list as well. PostgreSQL 17 follows the standard, where each column in the condition must reference a grouping column or sit inside an aggregate, so the alias is not recognized there.

Repeat the aggregate expression instead of the alias, as every example above does, and the query moves between MySQL, PostgreSQL, SQL Server and Oracle unchanged. The cost is one repeated expression per condition; the databases compute it once per group either way.

Common mistakes and optimization tips

Writing the aggregate in WHERE is the first mistake, and the database rejects the query rather than answering it:

SELECT region, COUNT(*)
FROM Orders
WHERE COUNT(*) > 5
GROUP BY region;

WHERE runs before the grouping, so there is nothing to count yet. Move the condition to HAVING.

Selecting a column that is neither grouped nor aggregated is the second, and it is rejected for a related reason: with three orders collapsed into one North row, the database has three order dates and one row to put them in. Group by the column, or wrap it in MIN(), MAX() or another aggregate.

Using HAVING where WHERE would do is the third, and the only one the database accepts in silence. A condition that reads a plain column belongs in WHERE, where it removes rows before the grouping work happens:

SELECT region, SUM(amount) AS total_sales
FROM Orders
WHERE status = 'Paid'
GROUP BY region
HAVING SUM(amount) > 500
ORDER BY total_sales DESC;

region	total_sales
East	900.00
North	750.00

East is 900.00 rather than 1250.00 here, because WHERE removed the pending order before the grouping. Each clause is doing the job it is meant for: rows out first, then groups, then the aggregate filter.

Use HAVING in DbSchema

DbSchema builds the grouped query for you and keeps the source rows one click away, which is the fastest way to see why a group did or did not survive the filter.

Connect through the MySQL JDBC driver or PostgreSQL JDBC driver and let DbSchema reverse-engineer the schema into a diagram. The connection, the diagram and the SQL Editor are in the free Community Edition.
Run the query in the SQL Editor with Execute Query, then run it again without the HAVING line to see every group before the filter. The two results side by side are the fastest explanation of what the clause removed.
To build the query with the mouse, open the Query Builder, turn on Group By mode from its toolbar, then right-click the column to aggregate and choose Aggregate for MIN, MAX, SUM, AVG or COUNT. DbSchema writes the GROUP BY query and updates it as you change the selection.
To see the orders behind a group, open the Relational Data Editor and click from the customer row into its orders. The Query Builder and the Relational Data Editor are in the Pro edition.

Both editors are stored in the model file and reopen with it, while the queries they generate run against the connected database.

Write one grouped query twice against your own data, once plain and once with a HAVING line on the aggregate, and the difference between the two result sets is the clause itself. DbSchema runs both in the SQL Editor, which the free Community Edition includes along with the connection and the diagram, while the Query Builder that assembles the GROUP BY is in Pro: https://dbschema.com/download.html. If the sort order of the surviving groups matters next, read SQL ORDER BY and SQL GROUP BY Explained.