Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 125 Β· Aggregation

HAVING

Learn how SQL HAVING filters groups after GROUP BY, when to use WHERE instead, and the logical order in which every part of a SELECT statement runs.

GROUP BY gives you one row per group. Very often you only want some of those groups: countries with at least four customers, products that sold more than ten units, customers who placed three or more orders. Those conditions talk about aggregates, and WHERE can't see aggregates.

HAVING is the filter for groups. It runs after the rows have been grouped and aggregated, so it can test COUNT(*), SUM(...) or any other aggregate. In this lesson you'll learn how to use it, how it differs from WHERE, and the logical order in which a database processes every clause of a SELECT. That order explains a lot of error messages you'll meet later.

The examples use the shop sample database.

Filtering groups with HAVING

Here are the customers per country, but only for countries with at least four customers:

SQL Countries with 4 or more customers Β· database: shop

Press Run (or Ctrl + Enter) to see the result here.

Five of the 13 countries pass: Colombia and Poland with 6 customers, then Australia, France and Turkey with 4. HAVING comes right after GROUP BY and before ORDER BY. Its condition can use the same operators as WHERE: comparisons, AND, OR, NOT, BETWEEN, IN and so on.

Why WHERE can't do it

Try moving the condition into WHERE. This example fails on purpose:

SQL An aggregate in WHERE (error) Β· database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQLite stops with Error: misuse of aggregate: COUNT(). Other databases word it differently (PostgreSQL says "aggregate functions are not allowed in WHERE"), but the reason is the same. WHERE looks at one row at a time before any groups exist. At that moment there is nothing to count yet.

So the rule of thumb is simple:

  • WHERE filters rows. It decides which rows go into the groups.
  • HAVING filters groups. It decides which groups appear in the result.

Using WHERE and HAVING together

Most real reports need both filters. Which customers have at least three delivered orders?

SQL Customers with 3+ delivered orders Β· database: shop

Press Run (or Ctrl + Enter) to see the result here.

Read it in the order the database works:

  1. WHERE status = 'delivered' throws away pending, shipped and cancelled orders.
  2. GROUP BY customer_id puts the remaining orders into one group per customer.
  3. HAVING COUNT(*) >= 3 keeps the groups with three or more rows.
  4. ORDER BY sorts what's left.

The two filters answer different questions, and swapping them changes the meaning. If you dropped the WHERE and wrote HAVING COUNT(*) >= 3 alone, you would count all orders, including cancelled ones.

HAVING can test aggregates you don't select

The aggregate in HAVING doesn't have to appear in the SELECT list. Which products have been ordered at least 30 times (counting units)? The query shows the revenue but filters on the quantity:

SQL Products with 30 or more units ordered Β· database: shop

Press Run (or Ctrl + Enter) to see the result here.

Five products qualify. Product 25 (the Jump Rope) sold the most units of all, 44, yet it brings in the least revenue of the five, which is why reports usually show both numbers.

You can also combine several conditions in one HAVING, for example HAVING COUNT(*) >= 5 AND AVG(quantity) > 2.

When a condition belongs in WHERE

HAVING may also test the grouping column itself, so both of these queries return the same rows:

SQL
-- Filter after grouping
SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country
HAVING country IN ('France', 'Poland');

-- Filter before grouping (better)
SELECT country, COUNT(*) AS customers
FROM customers
WHERE country IN ('France', 'Poland')
GROUP BY country;

Prefer the second form. A condition on plain columns belongs in WHERE: the database can discard rows early (often using an index) instead of building groups only to throw them away. Many optimizers rewrite the first form for you, but you shouldn't rely on that, and the WHERE version states your intent more clearly.

A quick test: if a condition makes sense for a single row, put it in WHERE. If it only makes sense for a group of rows (a count, a total, an average), put it in HAVING.

Finding duplicates

GROUP BY plus HAVING COUNT(*) > 1 is the standard way to find duplicate values. Which first names are shared by several customers?

SQL Duplicate first names Β· database: shop

Press Run (or Ctrl + Enter) to see the result here.

Ten first names are shared; Aiden, Maya, Rania, Rosa and Tara each belong to three customers. The same pattern finds duplicate emails before you add a UNIQUE constraint, or duplicate order lines after a buggy import. Group by every column that should be unique together, for example GROUP BY order_id, product_id to find a product listed twice in the same order.

The logical order of a SELECT

You write a SELECT statement in one order, but the database evaluates it in another. Conceptually, the clauses run like this:

Step Clause What it does
1 FROM (and joins) Picks the source rows
2 WHERE Keeps the rows that match
3 GROUP BY Forms groups
4 HAVING Keeps the groups that match
5 SELECT Computes the output columns and aliases (window functions run here)
6 DISTINCT Removes duplicate output rows
7 ORDER BY Sorts the output
8 LIMIT / OFFSET Keeps a slice of the sorted rows

This is a logical order: it defines what the result means. The engine may physically do things differently (for example, use an index to avoid sorting), but the answer must always be the same as if it had followed these steps.

The table explains several rules you've already seen:

  • WHERE can't use aggregates, because groups are formed in step 3.
  • ORDER BY can use column aliases from the SELECT list, because it runs after step 5.
  • In standard SQL, WHERE, GROUP BY and HAVING can't use SELECT aliases, because those aliases don't exist yet.

Aliases in HAVING

That last rule is where databases disagree. SQLite and MySQL let you refer to a SELECT alias in HAVING as a convenience:

SQL An alias in HAVING (SQLite) Β· database: shop

Press Run (or Ctrl + Enter) to see the result here.

PostgreSQL, SQL Server and Oracle reject that HAVING and ask you to repeat the expression: HAVING ROUND(SUM(shipping), 2) >= 20 (or simply HAVING SUM(shipping) >= 20).

Alias from SELECT used in… SQLite PostgreSQL MySQL SQL Server Oracle
ORDER BY Yes Yes Yes Yes Yes
GROUP BY Yes Yes Yes No No (23ai: yes)
HAVING Yes No Yes No No
WHERE No* No No No No

* SQLite does resolve an alias in WHERE when it can't find a real column with that name, but that's an unusual extension. Don't depend on it.

If your code has to run on more than one database, repeat the expression. It looks longer, but it works everywhere, and the optimizer computes it only once.

HAVING without GROUP BY

If a query has HAVING but no GROUP BY, the whole table is treated as one group. The result is then either one row or no rows:

SQL The whole table as one group Β· database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first query returns 17 because the condition holds; the second returns no rows. This is occasionally useful in data checks ("return something only if there are too many cancellations"), but you'll rarely need it. The SQL standard allows it, and SQLite accepts it since version 3.39.

Check your understanding

Quick check Β· 5 questions

of 5 answered You scored / 5 Β· perfect!

  1. 1. Which clause can filter on COUNT(*)?

    Aggregates only exist after grouping, so they can be tested in HAVING (or ORDER BY), never in WHERE.
  2. 2. You want customers with at least 2 orders, counting only orders from 2025-06 onward. Where does each condition go?

    The date is a property of each row, so WHERE removes earlier orders first. The count is a property of each group, so it belongs in HAVING.
  3. 3. In which logical order does a database process these clauses?

    Rows are picked and filtered, then grouped and filtered as groups, then the output columns are computed and finally sorted.
  4. 4. What does SELECT COUNT(*) FROM orders HAVING COUNT(*) > 1000; return on a table with 120 orders?

    Without GROUP BY the whole table is one group. Its count of 120 fails the condition, so the group is removed and the result is empty.
  5. 5. Why is a condition like country = 'France' better in WHERE than in HAVING?

    Both forms return the same rows, but filtering early avoids building groups that are thrown away and can use indexes.

Summary

  • HAVING filters groups after GROUP BY; WHERE filters rows before grouping.
  • Aggregates are not allowed in WHERE (misuse of aggregate in SQLite).
  • Use both together: WHERE for row conditions, HAVING for conditions on counts, sums and averages.
  • GROUP BY … HAVING COUNT(*) > 1 is the classic way to find duplicates.
  • Logical order: FROM β†’ WHERE β†’ GROUP BY β†’ HAVING β†’ SELECT β†’ DISTINCT β†’ ORDER BY β†’ LIMIT.
  • Aliases in HAVING work in SQLite and MySQL but not in PostgreSQL, SQL Server or Oracle; repeat the expression for portable code.

Next: Grouping in Practice, where you'll build real reports: monthly sales, top customers and percentages of a total.