SQL HAVING

The HAVING clause filters the results of a GROUP BY query based on conditions applied to aggregated values, such as COUNT(), SUM(), or AVG(). It exists because WHERE runs before grouping happens and simply cannot see aggregate results yet, so SQL needs a separate clause that runs after aggregation to filter whole groups instead of individual rows. Once you understand HAVING, you can answer questions like “which departments have more than two employees” or “which customers spent over $1,000 in total” — questions that WHERE alone cannot answer.

Overview: How HAVING Works

GROUP BY collapses many rows into groups and computes one aggregate value per group. HAVING is the clause that filters those groups — after aggregation has already happened — based on a condition, usually one that references an aggregate function.

This matters because of when each clause actually executes inside the database engine. A query is written in the order SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT, but it does not execute in that order. The engine’s logical processing order is:

1. FROM — read (and join) the base tables.
2. WHERE — filter individual rows, before any grouping happens.
3. GROUP BY — collapse the remaining rows into groups based on the grouping column(s).
4. Aggregate functions are computed — one value per group.
5. HAVING — discard whole groups whose aggregate values don’t satisfy the condition.
6. SELECT — compute the final output columns and expressions.
7. ORDER BY — sort the remaining groups/rows.
8. LIMIT — trim to the requested number of rows.

Because WHERE runs at step 2 — before grouping and before any aggregate exists — it has no way to reference COUNT(*), SUM(salary), or any other aggregate expression; those values simply don’t exist yet. HAVING exists to fill that gap: it runs at step 5, after each group’s aggregates have been computed, so it can compare those computed values against a condition. If a query has no GROUP BY at all, the engine treats the entire result set as one implicit group, so HAVING can still be used (rarely) to decide whether that single group is returned.

Syntax

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE row_condition
GROUP BY column1
HAVING aggregate_condition
ORDER BY column1;

Clause	Purpose
SELECT	Columns and aggregate expressions to return.
FROM	Source table(s), including any joins.
WHERE	Filters individual rows before grouping. Cannot reference aggregate functions.
GROUP BY	Groups rows that share the same value(s) in the listed column(s).
HAVING	Filters groups after aggregation. Typically uses an aggregate function.
ORDER BY	Sorts the final result set.

Standard SQL requires HAVING to come after GROUP BY and before ORDER BY. MySQL, PostgreSQL, SQL Server, and SQLite all support this exact syntax. One portability note: MySQL and SQLite are lenient about letting you reference a SELECT column alias inside HAVING (as the examples below do), but some strict engines and older SQL Server versions require you to repeat the full aggregate expression instead of the alias.

Examples

Example 1: Counting rows per group

This example builds a small employees table and finds departments with more than one employee.

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  salary REAL
);

INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Alice', 'Engineering', 95000),
(2, 'Bob', 'Engineering', 88000),
(3, 'Carol', 'Sales', 62000),
(4, 'Dave', 'Sales', 58000),
(5, 'Eve', 'Marketing', 71000);

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

Result:

department	num_employees
Engineering	2
Sales	2

SQLite groups the five rows into three departments (Engineering: 2 rows, Sales: 2 rows, Marketing: 1 row), computes COUNT(*) for each, then HAVING COUNT(*) > 1 discards Marketing because its count is only 1. Only groups that survive HAVING make it into the final output.

Example 2: Filtering by a sum

This example uses an orders table to find customers whose total spending exceeds $1,000.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_name TEXT,
  amount REAL
);

INSERT INTO orders (order_id, customer_name, amount) VALUES
(1, 'Acme Corp', 1200),
(2, 'Acme Corp', 800),
(3, 'Globex', 300),
(4, 'Globex', 250),
(5, 'Initech', 5000);

SELECT customer_name, SUM(amount) AS total_spent, COUNT(*) AS num_orders
FROM orders
GROUP BY customer_name
HAVING SUM(amount) > 1000
ORDER BY total_spent DESC;

Result:

customer_name	total_spent	num_orders
Initech	5000	1
Acme Corp	2000	2

Globex’s two orders total only 550, so its group fails the HAVING SUM(amount) > 1000 test and is dropped entirely — even though Globex has two rows, just like Acme Corp. Notice that HAVING filters on the aggregated total, not on any single order’s amount.

Example 3: Combining WHERE and HAVING

This is the most realistic pattern: WHERE removes rows you never want considered, then GROUP BY/HAVING summarize and filter what’s left.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT,
  category TEXT,
  price REAL,
  discontinued INTEGER
);

INSERT INTO products (product_id, product_name, category, price, discontinued) VALUES
(1, 'Widget A', 'Hardware', 25, 0),
(2, 'Widget B', 'Hardware', 45, 0),
(3, 'Old Gadget', 'Hardware', 15, 1),
(4, 'Sensor X', 'Electronics', 120, 0),
(5, 'Sensor Y', 'Electronics', 80, 0),
(6, 'Cable', 'Electronics', 10, 0);

SELECT category, ROUND(AVG(price), 2) AS avg_price, COUNT(*) AS num_products
FROM products
WHERE discontinued = 0
GROUP BY category
HAVING AVG(price) > 30
ORDER BY avg_price DESC;

Result:

category	avg_price	num_products
Electronics	70.0	3
Hardware	35.0	2

WHERE discontinued = 0 removes ‘Old Gadget’ before anything else happens, so Hardware’s average is computed from only Widget A and Widget B: (25 + 45) / 2 = 35. Electronics averages (120 + 80 + 10) / 3 = 70. Both groups pass HAVING AVG(price) > 30, so ORDER BY avg_price DESC simply sorts them, putting Electronics first.

How It Works Step by Step (Under the Hood)

Walking through Example 3 in the engine’s actual logical order:

• FROM products — the engine starts with all six rows.
• WHERE discontinued = 0 — row 3 (‘Old Gadget’) is removed. Five rows remain.
• GROUP BY category — the five remaining rows are bucketed into two groups: Hardware {25, 45} and Electronics {120, 80, 10}.
• Aggregates computed — AVG(price) and COUNT(*) are calculated per group: Hardware (35.0, 2), Electronics (70.0, 3).
• HAVING AVG(price) > 30 — both 35.0 and 70.0 satisfy the condition, so both groups survive. If Hardware’s average had been 28, that group would be dropped here, before it ever reaches SELECT.
• SELECT — the engine builds the output rows: category, rounded average, and count.
• ORDER BY avg_price DESC — the two remaining group-rows are sorted, placing Electronics (70.0) above Hardware (35.0).

The key insight is that HAVING operates on groups, not raw rows — by the time it runs, individual product rows no longer exist as separate entities within a group; only the aggregated summary does.

Common Mistakes

Mistake 1: Using an aggregate function inside WHERE

Because WHERE executes before grouping, it cannot use aggregate functions at all — this is a hard error, not just bad style.

SELECT department, COUNT(*) AS num_employees
FROM employees
WHERE COUNT(*) > 1
GROUP BY department;

This fails with an error such as “misuse of aggregate function COUNT()”, because at the point WHERE runs, no grouping or aggregation has happened yet — COUNT(*) has no meaning there. The fix is to move the condition into HAVING, which runs after aggregation:

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

Mistake 2: Using HAVING for a condition that doesn’t need aggregation

This version runs without error, but it’s inefficient and misleading: it groups every department, including Marketing, computes an average for each, and only discards Marketing’s group at the very end.

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING department <> 'Marketing'
ORDER BY department;

The condition department <> 'Marketing' doesn’t reference an aggregate at all — it’s a plain row-level condition, so there’s no reason to wait until after grouping to apply it. Filtering with WHERE instead removes Marketing’s rows before any grouping or aggregation work is done, which is both clearer and cheaper:

SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE department <> 'Marketing'
GROUP BY department
ORDER BY department;

Both versions return the same two rows, but the second one does less work and communicates intent correctly: use WHERE for row-level conditions, and reserve HAVING for conditions on aggregated values.

Best Practices

• Use WHERE to filter individual rows whenever the condition doesn’t involve an aggregate function — it’s applied earlier and is almost always cheaper.
• Reserve HAVING for conditions that genuinely need an aggregate, such as COUNT(*) > n, SUM(x) > n, or AVG(x) BETWEEN a AND b.
• Combine WHERE and HAVING in the same query when you need both: WHERE to exclude rows before grouping, HAVING to exclude groups after aggregating.
• Always pair GROUP BY/HAVING queries with an explicit ORDER BY if row order matters — SQL does not guarantee group order otherwise.
• If you find yourself repeating a complex aggregate expression in both SELECT and HAVING, check whether your database dialect allows referencing the SELECT alias in HAVING (SQLite and MySQL do) to keep the query readable.
• Remember that every non-aggregated column in SELECT must appear in GROUP BY — this rule is enforced strictly in most databases and prevents ambiguous per-group values.

Practice Exercises

Exercise 1

Using the employees table from Example 1 (Alice, Bob, Carol, Dave, Eve), write a query that returns each department’s total salary, but only for departments whose total salary exceeds $150,000.

Hint: use SUM(salary) in both SELECT and HAVING. Expected result: only Engineering qualifies, with a total of 183000.

Exercise 2

Using the orders table from Example 2, write a query that lists every customer who placed more than one order, along with how many orders each one placed.

Hint: group by customer_name and filter on COUNT(*). Expected result: Acme Corp (2 orders) and Globex (2 orders); Initech is excluded because it only has one order.

Exercise 3

Using the products table from Example 3, but this time without the WHERE discontinued = 0 filter, write a query that finds categories where the most expensive product (MAX(price)) is under 100.

Hint: group by category and filter with HAVING MAX(price) < 100. Expected result: only Hardware qualifies (its most expensive product is 45); Electronics is excluded because Sensor X costs 120.

Summary

• HAVING filters groups created by GROUP BY, based on aggregated values — WHERE filters individual rows before grouping.
• The logical execution order is FROM → WHERE → GROUP BY → aggregates computed → HAVING → SELECT → ORDER BY → LIMIT.
• Aggregate functions like COUNT(), SUM(), and AVG() cannot be used in WHERE because they don’t exist yet at that stage — that restriction is exactly why HAVING exists.
• Use WHERE for row-level conditions and HAVING only for conditions on aggregated group values; mixing them up still runs in some cases but wastes work.
• WHERE and HAVING can be combined in one query: WHERE trims rows first, HAVING trims the resulting groups afterward.
• Always add an explicit ORDER BY when the order of grouped results matters, since it isn’t guaranteed by default.