HAVING in SQL: filtering groups after aggregation, examples and pitfalls

HAVING filters groups. WHERE decides which rows enter the calculation at all, GROUP BY splits them into groups, and HAVING looks at the aggregated result and keeps only the groups that meet the condition. Typical questions: which customers spent at least 500, which categories have at least two buyers, on which days there were more than a hundred orders.

The syntax itself is short. The trouble starts at the edges: a value equal to the threshold, NULL in a sum, an alias that works in one engine and fails in another. That is what I focus on here. If you are new to grouping, start with the GROUP BY entry or the article SQL basics: from your first table to a result you can explain, where HAVING appears as part of a complete query.

Syntax and logical order

SELECT grouping_column, AGGREGATE(column)
FROM table_name
WHERE row_condition
GROUP BY grouping_column
HAVING group_condition
ORDER BY grouping_column;

I write a HAVING condition the same way as a WHERE condition: comparisons, AND, OR, BETWEEN, IN. The difference is what I can check. HAVING sees the GROUP BY columns and the results of aggregates such as SUM or COUNT. An aggregate used in HAVING does not have to appear in SELECT.

The order in which I write the clauses differs from the order in which the database logically processes them:

1. FROM and JOIN: where the rows come from.
2. WHERE: which rows enter the calculation.
3. GROUP BY: how they are split into groups.
4. Aggregates: SUM, COUNT, AVG calculated for each group separately.
5. HAVING: which groups stay.
6. SELECT: what I show and under which names.
7. ORDER BY, and LIMIT at the very end.

This is the logical order. The query planner may physically do something smarter, but the result has to look as if it followed this list. Two rules follow from it, and they save me from most mistakes. WHERE has no sums or counts yet, because the groups do not exist. A SELECT alias is created only after HAVING, so standard SQL does not let you use it there. The exceptions are below.

Example: customers who spent at least 500

I use a small, synthetic orders table. Amounts are whole units to keep the results readable. Two of Dan’s orders have no amount, and one of Ben’s orders is cancelled. Both details matter later.

-- example: fixture
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  category TEXT NOT NULL,
  amount INTEGER,
  status TEXT NOT NULL
);

INSERT INTO orders (order_id, customer, category, amount, status) VALUES
  (1, 'Ada', 'books', 120, 'paid'),
  (2, 'Ada', 'books', 80, 'paid'),
  (3, 'Ada', 'games', 300, 'paid'),
  (4, 'Ben', 'games', 500, 'paid'),
  (5, 'Ben', 'games', 150, 'cancelled'),
  (6, 'Cleo', 'books', 200, 'paid'),
  (7, 'Cleo', 'music', 350, 'paid'),
  (8, 'Dan', 'music', NULL, 'paid'),
  (9, 'Dan', 'music', NULL, 'paid'),
  (10, 'Eva', 'books', 40, 'paid');

I want the customers whose paid orders add up to at least 500:

-- example: basic
SELECT customer, SUM(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(amount) >= 500
ORDER BY customer;

Ben has orders worth 650 in the table, but 150 of that is a cancelled order. WHERE removed that row before anything was summed, so HAVING only saw 500. If I deleted the WHERE line, Ben would have 650. That is how I check whether a condition sits in the right place. The status describes a single order, so it goes into WHERE. The 500 threshold describes a customer’s total, so it goes into HAVING.

Dan and Eva are missing from the result. Eva has 40, and Dan’s sum is empty. I come back to Dan in the NULL section.

A value equal to the threshold

The same condition with a strict comparison:

-- example: strict
SELECT customer, SUM(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(amount) > 500
ORDER BY customer;

Ada and Ben have exactly 500, so > 500 drops them. Sounds trivial, yet in reports it is a common reason why two numbers disagree: the requirement says “over 500” and someone reads it as “from 500”. Before I write the condition, I ask what should happen to a value equal to the threshold. BETWEEN 100 AND 500 includes both ends, so it would also keep Ada and Ben.

I combine conditions with AND and OR, just like in WHERE. Here I add the number of orders, which I do not even show in the result:

-- example: multi
SELECT customer, SUM(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(amount) >= 500 AND COUNT(*) >= 2
ORDER BY customer;

Ben drops out because only one order is left once the cancelled one is excluded.

HAVING without GROUP BY

HAVING does not require GROUP BY. Without it, the whole set after WHERE is one group, and the query returns one row when the condition holds or zero rows when it does not.

-- example: whole-table
SELECT COUNT(*) AS paid_orders, SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid'
HAVING COUNT(*) >= 5;

Change the threshold to >= 10 and the result is empty. Without HAVING, a query like this always returns exactly one row, even on an empty table, where COUNT(*) gives 0. I find that useful in data quality checks: the summary appears only when there is enough data.

This works in PostgreSQL, MySQL and SQL Server. SQLite allows HAVING without GROUP BY since version 3.39.0 from 2022, and older versions raise an error. That is where the claim “HAVING requires GROUP BY” in older tutorials comes from.

Aliases in HAVING depend on the engine

It is tempting to write it shorter:

-- example: alias
SELECT customer, SUM(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING total >= 500
ORDER BY customer;

SQLite 3.53.1 and MySQL 8.4 return the same result as the first example: Ada, Ben and Cleo. PostgreSQL 17 stops with an error:

ERROR:  column "total" does not exist

SQL Server will not accept it either. According to the Microsoft documentation, a SELECT alias is available only to clauses processed later, such as ORDER BY. MySQL explicitly describes an alias in HAVING as its own extension to the standard.

In code that has to run on more than one engine I have two options. I repeat the aggregate expression, as in the first example. Or I calculate the sum in a subquery and filter the alias with a plain WHERE outside:

-- example: portable
SELECT customer, total
FROM (
  SELECT customer, SUM(amount) AS total
  FROM orders
  WHERE status = 'paid'
  GROUP BY customer
) AS totals
WHERE total >= 500
ORDER BY customer;

For longer calculations I prefer the second option, usually written as a CTE with WITH. I calculate the expression once, and the condition reads like a sentence.

NULL, duplicates and COUNT(DISTINCT)

Aggregates ignore NULL. SUM(amount) for Ada is an ordinary sum, but for Dan, who has only empty amounts, the result is NULL rather than 0. A comparison with NULL is neither true nor false, so HAVING drops the group. You can see it when asking for customers with a small total:

-- example: null-sum
SELECT customer, SUM(amount) AS total, COUNT(*) AS orders
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(amount) < 100
ORDER BY customer;

Dan has two paid orders and is not in the result. If a missing amount should count as zero, I write HAVING COALESCE(SUM(amount), 0) < 100 (more in the COALESCE entry). Usually, though, I first check where the empty amounts came from, because zero and missing data tell you different things. The difference between COUNT(*) and COUNT(column) helps here: the first counts rows, the second only values that are not NULL.

-- example: missing-amounts
SELECT customer, COUNT(*) AS orders, COUNT(amount) AS priced
FROM orders
GROUP BY customer
HAVING COUNT(amount) < COUNT(*)
ORDER BY customer;

Duplicates set a similar trap. COUNT(*) >= 2 means “at least two rows”, not “at least two different things”. When I ask which customers bought in at least two categories, I count distinct values:

-- example: distinct-categories
SELECT customer, COUNT(*) AS orders, COUNT(DISTINCT category) AS categories
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING COUNT(DISTINCT category) >= 2
ORDER BY customer;

With HAVING COUNT(*) >= 2 Dan would join the result: he has two orders, both in the same category. COUNT(DISTINCT ...) also ignores NULL, so an empty category would not be counted as a separate value.

Common mistakes and when to use something else

An aggregate in WHERE. This is the most common mistake at the start:

-- example: aggregate-in-where
SELECT customer, SUM(amount) AS total
FROM orders
WHERE SUM(amount) >= 500
GROUP BY customer;

SQLite answers misuse of aggregate: SUM(), and PostgreSQL aggregate functions are not allowed in WHERE. When WHERE runs, the groups do not exist yet, so there is nothing to sum. I move the condition to HAVING.

A single-row condition in HAVING. HAVING status = 'paid' while grouping by customer looks harmless. PostgreSQL rejects it with a message that status must appear in GROUP BY or in an aggregate. SQLite runs it and checks the status of one arbitrarily chosen row from the group, so the result depends on chance. I check the status in WHERE.

A condition on the grouping column in HAVING. HAVING customer <> 'Eva' works correctly everywhere, but I prefer it in WHERE. The PostgreSQL documentation points out that the database then does not have to group and aggregate rows that would be dropped anyway. And the reader sees right away what enters the analysis.

I need individual rows, not groups. HAVING returns one row per group. If I want to see all orders of customers who spent at least 500, I join the table with a subquery that uses HAVING, or I calculate the sum with a window function SUM(amount) OVER (PARTITION BY customer) and filter it in an outer query.

Short exercise

On the same table, find the categories in which at least two different customers placed paid orders and the total of those orders is at least 400. If everything is right, you get two categories. Then change the threshold to > 440 and check which category dropped out and why. Finally, remove WHERE and see whether Ben’s cancelled order changes the list of categories or only one of the totals.