HAVING in SQL: Filtering Grouped Results

Short answer

• HAVING in SQL filters groups after GROUP BY and aggregation. WHERE filters single rows before grouping, and HAVING filters the grouped results that remain.
• The standard clause order is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT. HAVING comes after GROUP BY and before ORDER BY.
• Use HAVING when the test needs an aggregate function, such as COUNT, SUM, AVG, MIN, or MAX. Use WHERE when the test applies to a single row, such as a status value or a date.
• Do not put an aggregate function in WHERE. A test such as WHERE COUNT(*) > 1 fails, because the count does not exist at the row filtering stage. Write the same test in HAVING instead.
• A group that fails the HAVING test disappears completely. It is not shown with a zero. If you need a zero row for a group with no matching rows, that is a different problem, and it usually needs an outer join.
• For portable code, repeat the aggregate expression in HAVING, for example HAVING SUM(amount) > 250, rather than relying on a SELECT alias. MySQL allows an alias in HAVING, but repeating the expression works more widely.

If you searched for “having by in sql”, you almost certainly mean HAVING used with GROUP BY. There is no clause called “HAVING BY” in SQL. The correct pairing is GROUP BY for forming groups and HAVING for filtering those groups, and the two are often typed together because they are almost always used together.

That pairing solves a problem WHERE cannot solve. WHERE filters single rows before grouping, so it cannot test a total, a count, or an average, because those values do not exist yet for a single row. HAVING runs after grouping and aggregation, so it can test exactly those values. If you want customers with more than one order, categories with total sales above a threshold, or departments whose average salary passes a limit, HAVING states the test.

This guide works through one small orders dataset again and again, so every result can be checked by hand. It covers syntax in clause order, HAVING versus WHERE, WHERE and HAVING together, filtering on each common aggregate, HAVING without GROUP BY, HAVING with a join, NULL behavior, dialect differences, and the most common mistakes. It stays focused on HAVING. It does not become a general guide to joins or to CASE expressions, which are covered separately.

SHORT-ANSWER BOX

What HAVING Does

Think of a grouped query in three stages. First, FROM supplies the rows. Second, WHERE removes single rows that fail its test, and GROUP BY collects the survivors into groups sharing the same grouping values. Third, the database computes one aggregate per group, and HAVING keeps only the groups that pass its test.

PostgreSQL documentation states the same rule: WHERE filters individual rows before GROUP BY, while HAVING filters group rows created by GROUP BY. Each column in HAVING must reference a grouping column, unless it appears within an aggregate function. So HAVING can test a grouping column or an aggregate, but not an ordinary non-grouping column, which has many values inside one group.

Read HAVING as “keep only groups where” and WHERE as “keep only rows where”. Group totals and averages belong in HAVING; single row status and date tests belong in WHERE.

Read the Syntax in Clause Order

The standard form used in this guide is:

SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING SUM(amount) > 200
ORDER BY total_amount DESC;

SELECT names the columns to return, FROM names the table, WHERE filters single rows, GROUP BY forms the groups, HAVING filters the groups, ORDER BY sorts the survivors, and LIMIT restricts how many are returned.

Clause order matters. MySQL documentation states that clauses must be given in exactly the order shown, so HAVING must come after GROUP BY and before ORDER BY. Writing HAVING before GROUP BY is a syntax error. The same order holds in PostgreSQL and in SQLite, where the clauses run SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT.

Written order differs from logical order. You write SELECT first, but the database filters with WHERE, groups, computes aggregates, and applies HAVING before finalizing the SELECT list and sort. That is why an aggregate cannot appear in WHERE.

The Sample Tables Used Throughout This Guide

Every example uses the same tables, so each result can be traced to the same source rows. Customers:

Orders:

Finn Cole has no orders, so he produces no group in a query grouped from the orders table alone: GROUP BY creates one group per value that actually appears in the grouped rows. Eva Lind has one cancelled order, so when WHERE keeps only completed orders, she forms no group either.

First Grouped Query: No HAVING Yet

Before adding a filter, look at the groups themselves. This query groups all ten orders by customer:

SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_amount,
       AVG(amount) AS average_amount, MIN(amount) AS smallest_amount,
       MAX(amount) AS largest_amount
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Result:

Check the arithmetic by hand. Ana Ruiz: 120.00 plus 80.00 plus 200.00 equals 400.00, and 400.00 divided by 3 equals 133.33 rounded to two decimal places. Ben Carter totals 300.00 across two orders. Carla Moss has one order of 300.00. Dan Okafor totals 190.00 across three orders. Eva Lind has one order of 15.00.

This unfiltered result is the starting point for every HAVING example that follows. HAVING never changes groups or aggregates; it only removes whole groups.

First HAVING Example: Groups With More Than One Order

This query keeps only customers with more than one order:

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

Result: Ana Ruiz with 3 orders, Ben Carter with 2, and Dan Okafor with 3. Carla Moss and Eva Lind disappear, each with a count of 1. Finn Cole does not appear either: he never formed a group. HAVING cannot create a group for a value with no rows. A report listing every customer needs a left join from customers to orders, then grouping.

Test the boundary: HAVING COUNT() >= 2 returns the same three customers, while HAVING COUNT() > 2 returns Ana Ruiz and Dan Okafor only, because Ben Carter has exactly 2 orders. Always test with a group on the threshold.

HAVING Versus WHERE: The Core Distinction

The most common error is writing an aggregate test in WHERE. This query fails:

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE COUNT(*) > 1
GROUP BY customer_id;

It fails for a logical reason. At the stage where WHERE runs, the database is still looking at single order rows. No count exists yet, because the rows have not been grouped. The corrected query moves the test to HAVING:

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

The reverse error is subtler: writing a single row test in HAVING when it belongs in WHERE. For example, GROUP BY customer_id HAVING customer_id > 2 returns Carla Moss, Dan Okafor, and Eva Lind, but the same result is clearer as WHERE customer_id > 2, which removes the other rows before grouping starts. Rule of thumb: one row alone decides it, use WHERE; the whole group decides it, use HAVING.

Using WHERE and HAVING Together

Most real queries need both clauses. WHERE first narrows the rows, then GROUP BY forms groups from the survivors, then HAVING filters those groups. A row removed by WHERE never contributes to any aggregate.

Ask a sharper question: among completed orders only, which customers have a completed total above 200.00?

SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING SUM(amount) > 200
ORDER BY customer_id;

WHERE keeps only the eight completed rows and removes order 103, Ana Ruiz cancelled order of 200.00, and order 110, Eva Lind cancelled order of 15.00. GROUP BY then forms four groups, and the completed totals are: Ana Ruiz 200.00, Ben Carter 300.00, Carla Moss 300.00, and Dan Okafor 190.00. HAVING SUM(amount) > 200 keeps only Ben Carter and Carla Moss.

Without the WHERE filter, Ana Ruiz totals 400.00 and passes easily. With it, her cancelled order is removed before aggregation, her total falls to 200.00, and she fails the strict greater than test. That is correct: the question changed from all orders to completed orders. When debugging a missing group, check WHERE first: it may never have formed a group at all.

Filtering on SUM, AVG, MIN, and MAX

COUNT answers how many rows are in the group. The other aggregates answer different questions. All queries in this section run on all ten orders, with no WHERE filter, so the group values are the ones in the unfiltered table above.

SUM: customers whose total is above 250.00. Query: SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id HAVING SUM(amount) > 250. Result: Ana Ruiz 400.00, Ben Carter 300.00, Carla Moss 300.00. Dan Okafor is excluded despite three orders, because his total is only 190.00. A SUM filter cares about size, not frequency.

AVG: customers whose average order is above 100.00. Query: SELECT customer_id, AVG(amount) FROM orders GROUP BY customer_id HAVING AVG(amount) > 100. Result: Ana Ruiz 133.33, Ben Carter 150.00, Carla Moss 300.00. Dan Okafor is excluded, because his average is 63.33, despite frequent buying. Watch rounding: 400.00 divided by 3 is 133.333 repeating, and 133.33 is only an approximation.

MIN: customers whose smallest order is at least 50.00. Query: SELECT customer_id, MIN(amount) FROM orders GROUP BY customer_id HAVING MIN(amount) >= 50. Result: Ana Ruiz 80.00, Ben Carter 50.00, Carla Moss 300.00. Dan Okafor fails because his smallest order is 40.00. A MIN test asks whether every order in the group meets a floor.

MAX: customers whose largest order is at least 200.00. Query: SELECT customer_id, MAX(amount) FROM orders GROUP BY customer_id HAVING MAX(amount) >= 200. Result: Ana Ruiz 200.00, Ben Carter 250.00, Carla Moss 300.00. Ana Ruiz passes on the boundary through her cancelled order of 200.00. For completed orders only, a WHERE filter would be needed first, and her largest completed order is 120.00, so she would fail. A MAX test is a “at least one large value” test.

Combining Conditions, ORDER BY, and LIMIT

A HAVING clause can combine several tests with AND and OR, each evaluated against the same group. Parentheses control how tests group, and they matter as much here as in WHERE.

AND is strict: every test must pass. This query keeps customers with at least two orders and a total above 250.00:

SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2 AND SUM(amount) > 250;

Result: Ana Ruiz with 3 orders and 400.00, and Ben Carter with 2 orders and 300.00. Dan Okafor passes the count test but fails the sum test, with 190.00. Carla Moss passes the sum test but fails the count test, with one order.

OR is looser: the group survives when any test passes. This query keeps customers with at least three orders or a total of at least 300.00:

SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 3 OR SUM(amount) >= 300;

Result: Ana Ruiz, Ben Carter, Carla Moss, and Dan Okafor. Only Eva Lind fails both, with one order and a total of 15.00. When mixing AND and OR, use parentheses to state the intended grouping explicitly. You can also use BETWEEN, IN, and NOT in HAVING on aggregate values, for example HAVING SUM(amount) BETWEEN 200 AND 350, endpoints included.

After HAVING removes the failing groups, ORDER BY sorts the survivors, and LIMIT restricts how many sorted groups are returned. This query finds the top two customers by total, among customers with at least two orders:

SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY total_amount DESC
LIMIT 2;

Result: Ana Ruiz with 400.00, then Ben Carter with 300.00. HAVING runs before the sort and the limit: the count failures are removed first, then the survivors are sorted, then the top two are taken.

HAVING Without GROUP BY: The Single Group Case

PostgreSQL documentation describes an important special case: the presence of HAVING turns a query into a grouped query even if there is no GROUP BY clause. All selected rows are considered to form a single group, and the SELECT list and HAVING clause can only reference table columns from within aggregate functions. Such a query emits a single row if the HAVING condition is true, and zero rows if it is not true.

This query asks whether the whole orders table passes a single test:

SELECT COUNT(*) AS order_count
FROM orders
HAVING COUNT(*) > 5;

Result: one row, with order_count 10. Change the threshold to a value the table cannot pass, and the same query returns zero rows, not a row with a zero:

SELECT COUNT(*) AS order_count
FROM orders
HAVING COUNT(*) > 10;

Result: no rows returned. A group that fails the test disappears, and here there is only one group. If you need a row even when the test fails, compute the aggregate without HAVING and test the value in application code, or use a CASE expression in the SELECT list to label it. HAVING is for filtering, not for labeling.

Dialect caution: the single group behavior is documented for PostgreSQL. MySQL also permits HAVING without GROUP BY in practice, but portable code should not depend on it. In SQLite, prefer an explicit GROUP BY. When you mean groups, write GROUP BY.

HAVING With a Join

HAVING works the same way when the grouped rows come from a join: the join combines rows, GROUP BY groups them, and HAVING filters the groups. This query joins customers to orders and keeps customers with more than one order. It uses an inner join, so Finn Cole, who has no orders, does not appear:

SELECT c.customer_id, c.customer_name, COUNT(o.order_id) AS order_count,
       SUM(o.amount) AS total_amount
FROM customers AS c
INNER JOIN orders AS o
  ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(o.order_id) > 1
ORDER BY c.customer_id;

Result:

Grouping by both customer_id and customer_name is deliberate: the identifier is the true key, and the name is included so it can be selected safely. Grouping by name alone would merge two customers who shared a name.

Note COUNT(o.order_id) rather than COUNT(). In an inner join the two agree, because every joined row has a matching order row. With an outer join they differ: a customer with no orders produces a joined row with NULL order columns, which COUNT() counts but COUNT(o.order_id) does not. With a left join, Finn Cole would appear with a count of zero under COUNT(o.order_id) and a total of NULL under SUM(o.amount), because SUM over no values is NULL, not zero.

NULL Values, COUNT, and Aggregates

NULL handling changes aggregate values, and therefore HAVING results. As documented for PostgreSQL value expressions, most aggregates ignore NULL inputs, while COUNT(*) counts rows. COUNT(column) counts only non-NULL values, and SUM, AVG, MIN, and MAX ignore NULL values.

The sample dataset has no NULL amounts, so every example above is unaffected. Imagine one extra order for Dan Okafor with a NULL amount. His group would then have four rows. COUNT() would be 4, but COUNT(amount) would be 3. SUM(amount) would still be 190.00, and AVG(amount) would still be 63.33, computed over the three non-NULL amounts. So COUNT() and COUNT(amount) tests can give different answers for the same group.

Two practical rules follow. First, use COUNT(*) for how many rows are in the group, and COUNT(column) for how many rows have a value in that column. Second, when a column can be NULL, decide before writing HAVING whether NULL rows should count toward frequency or be excluded by a WHERE filter first, such as WHERE amount IS NOT NULL.

Common Mistakes and How to Fix Them

Mistake 1: putting an aggregate in WHERE. Wrong: WHERE SUM(amount) > 250. Right: GROUP BY customer_id HAVING SUM(amount) > 250.

Mistake 2: putting a single row filter in HAVING when it belongs in WHERE. Wrong: GROUP BY customer_id HAVING status = ‘completed’. Right: WHERE status = ‘completed’ GROUP BY customer_id.

Mistake 3: selecting a non-grouping, non-aggregated column. Wrong: SELECT customer_id, order_date, COUNT(*) FROM orders GROUP BY customer_id. The order_date column has many values inside one group, so there is no single value to display. Right: add order_date to GROUP BY, aggregate it with MIN or MAX, or drop it. Strict systems reject the wrong form. Lenient systems may return an arbitrary value, which looks plausible while being meaningless.

Mistake 4: expecting a zero row for a missing group. Wrong expectation: a HAVING query over orders will list Finn Cole with a count of zero. Right: no group exists for him, because he has no order rows. To list every customer, start from customers with a left join to orders, and use COUNT(o.order_id) so he counts as zero.

Mistake 5: confusing COUNT() with COUNT(column). Wrong: using COUNT(amount) to count orders when some amounts are NULL. Right: use COUNT() to count rows, and COUNT(amount) only for rows with a non-NULL amount. In this dataset the two agree, because no amount is NULL, but that is a property of this dataset, not a general rule.

Mistake 6: forgetting that WHERE runs before aggregation. Wrong expectation: filtering on status in HAVING will still let cancelled orders contribute to the total. Right: put the status test in WHERE when cancelled orders must not contribute. The completed totals example shows Ana Ruiz falling from 400.00 to 200.00 when cancelled orders are removed first.

Mistake 7: relying on a SELECT alias in HAVING for portable code. Wrong: HAVING total_amount > 250, where total_amount is a SELECT alias. Right: HAVING SUM(amount) > 250. MySQL permits the alias form, but repeating the expression works more widely.

Mistake 8: testing an average against a rounded display value. Wrong: assuming that an average displayed as 133.33 is exactly 133.33. Right: keep thresholds away from repeating decimals, or apply the same rounding in the HAVING test as in the SELECT list.

Dialect Differences: PostgreSQL, MySQL, and SQLite

The core behavior of HAVING is the same across the three systems covered here: it filters groups after GROUP BY, it can test aggregates, and it comes after GROUP BY and before ORDER BY. The differences are at the edges.

The practical lesson is to write the strict, explicit form always: group by every non-aggregated column you select, repeat aggregate expressions in HAVING, put single row filters in WHERE, and use GROUP BY even when a single group test might work without it.

FAQ

What does HAVING do in SQL?

HAVING filters groups after GROUP BY and aggregation, keeping only groups whose condition is true. WHERE filters single rows before grouping.

What is the difference between HAVING and WHERE in SQL?

WHERE filters individual rows before GROUP BY and cannot test aggregates. HAVING filters whole groups after GROUP BY and can test COUNT, SUM, AVG, MIN, and MAX. Use WHERE for row level tests such as status, and HAVING for group level tests such as a total.

Do I need GROUP BY to use HAVING?

In normal use, yes. PostgreSQL also documents a single group case, where HAVING without GROUP BY treats the whole result as one group and returns either one row or zero rows. For portable code, write GROUP BY whenever you mean grouped filtering.

Why does my query fail when I put COUNT in WHERE?

It fails because WHERE runs before grouping, so the count does not exist yet. Move the test to HAVING, after GROUP BY. The corrected form is GROUP BY customer_id HAVING COUNT(*) > 1.

Can HAVING test a column that is not aggregated?

HAVING can test a grouping column, because that value defines the group. It cannot meaningfully test an ordinary non-grouping column, which has many values inside one group. Put single row tests in WHERE instead.

Why does a customer with no orders not appear in my HAVING result?

A grouped query over orders creates no group for a customer with no order rows. To list every customer, start from customers with a left join to orders, group by customer, and use COUNT on the order identifier so the unmatched customer counts as zero. Note that SUM over no values is NULL, not zero.

Can I use a SELECT alias in HAVING?

MySQL documentation permits an alias in HAVING, so HAVING total_amount > 250 may work in MySQL when total_amount is a SELECT alias for SUM(amount). Other systems are stricter, so portable code repeats the aggregate expression in HAVING instead.

Can HAVING and WHERE be used in the same query?

Yes, and most real grouped queries use both. WHERE first removes single rows, then GROUP BY forms groups from the survivors, then HAVING removes groups that fail the group level test. The completed orders example shows this: WHERE status = ‘completed’ removes cancelled orders first, and HAVING SUM(amount) > 200 then keeps only Ben Carter and Carla Moss.

What happens if no group passes the HAVING test?

The query returns zero rows. A failed group disappears completely; it is not shown with a zero count or total. The same rule applies to the single group case without GROUP BY.

Keep reading: Inner Join in SQL: How Matching Rows Work, With Worked Examples

Keep reading: COUNT of COUNT in SQL: Counting the Counts

Keep reading: SQL Queries With CASE: Conditional Logic in a Query

Sources & references

• PostgreSQL Documentation, SELECT reference, HAVING Clause section: WHERE filters individual rows before GROUP BY, while HAVING filters group rows created by GROUP BY; each column in HAVING must reference a grouping column unless it appears within an aggregate function; HAVING without GROUP BY turns the query into a grouped query over a single group, returning one row if the condition is true and zero rows if not. https://www.postgresql.org/docs/current/sql-select.html
• PostgreSQL Documentation, Value Expressions, aggregate expressions section: most aggregate functions ignore NULL inputs, while COUNT(*) counts rows; COUNT on a column counts non-NULL values. https://www.postgresql.org/docs/devel/sql-expressions.html
• MySQL 8.0 Reference Manual, SELECT Statement: clause order with HAVING after GROUP BY and before ORDER BY, the rule that clauses must be given in exactly the order shown, and the rule that a SELECT alias can be used in GROUP BY, ORDER BY, or HAVING. https://dev.mysql.com/doc/refman/8.0/en/select.html
• SQLite GROUP BY guidance, as summarized from the SQLite tutorial sources checked for this article: clause order SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, with HAVING filtering grouped results after aggregation. https://www.sqlitetutorial.net/sqlite-group-by
• SERP check, literal phrase "having by in sql", checked 1 Nov 2026: top results were a GROUP BY and HAVING guide, a university SQL HAVING page, a HAVING in SQL statement page, a HAVING explainer, the W3Schools SQL HAVING page, a GROUP BY with HAVING and aggregates guide, and a HAVING versus WHERE comparison. Common pattern: definition plus one or two table examples, with less sustained attention to WHERE and HAVING used together, hand checked arithmetic across a reused dataset, boundary thresholds, NULL and COUNT distinctions, and labeled dialect differences. This article differentiates on a single reused dataset, stage by stage WHERE then HAVING reasoning, and explicit debugging steps for missing groups and wrong totals.