SQL HAVING Clause

SQL HAVING filters the groups created by GROUP BY after the aggregate values have been calculated. Unlike WHERE, which filters individual rows before grouping, HAVING filters the resulting groups. Here is "which departments have more than 12 employees?":

SELECT department,
       COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 12;

▶ Run this query in the SQL Playground

1. GROUP BY department creates the groups — the 120 employee rows become 10 groups, one per department.
2. COUNT(*) calculates a value for each group — the number of employees in it.
3. HAVING COUNT(*) > 12 filters those groups — only the 4 departments whose count is above 12 are returned.

What HAVING Kept and What It Removed

Run the same query without the HAVING line and you get all ten groups. HAVING tests each of them once:

HAVING never looks at a single employee. It only sees the ten summary rows, and it can test them with a number — the count — that did not exist until the groups were built.

HAVING builds directly on two earlier lessons: how GROUP BY creates SQL groups and how WHERE filters rows before aggregation.

HAVING is written after GROUP BY and before ORDER BY, followed by a condition that is tested once per group:

SELECT   column_name,
         aggregate_function(column)
FROM     table_name
WHERE    row_condition            -- optional: filter rows first
GROUP BY column_name
HAVING   group_condition          -- filter the groups
ORDER BY column_name;             -- optional: sort what is left

What a HAVING Condition Can Contain

• An aggregate compared with a value — HAVING SUM(total) > 30000.
• An aggregate that is not in the SELECT list — SELECT department … HAVING COUNT(*) > 12 returns the same 4 departments without showing the count.
• Any comparison operator — =, <>, >=, BETWEEN, IN. HAVING COUNT(*) BETWEEN 10 AND 11 returns 4 departments.
• Several conditions joined with AND / OR — see multiple conditions.
• A subquery — see HAVING with a subquery.
• A column that is in GROUP BY — allowed, but that kind of condition usually belongs in WHERE (why).

Where HAVING Goes: Clause Order

SELECT department, COUNT(*)
FROM employees
HAVING COUNT(*) > 12
GROUP BY department;

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 12;

GROUP BY makes the groups. HAVING decides which of them you see.

Step 1 — group. The orders table has 200 rows. Grouping by status turns them into 6 summary rows:

SELECT status,
       COUNT(*) AS orders
FROM orders
GROUP BY status;

▶ Run this query in the SQL Playground

Step 2 — filter the groups. Adding one line keeps only the statuses with at least 25 orders:

SELECT status,
       COUNT(*) AS orders
FROM orders
GROUP BY status
HAVING COUNT(*) >= 25;

▶ Run this query in the SQL Playground

200 rows became 6 groups, and HAVING kept 3 of them. Every HAVING query follows this shape — the only things that change are the grouping column, the aggregate and the threshold. If the grouping step itself is new to you, start with the SQL GROUP BY lesson; this page concentrates on the filter.

The aggregate you choose decides what question the filter answers.

HAVING with COUNT()

To use HAVING with COUNT, group the rows and compare COUNT(*) — the number of rows in each group — with a threshold. Which customers have placed four or more orders?

SELECT user_id,
       COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 4
ORDER BY order_count DESC;

▶ Run this query in the SQL Playground

114 customers have at least one order; 6 of them reach four. Typical use: repeat customers, busy stores, and — with HAVING COUNT(*) > 1 — duplicate values.

COUNT(*) vs COUNT(column) in HAVING

COUNT(*) counts every row in the group. COUNT(column) counts only the rows where that column is not NULL. In the employees table, manager_id is NULL for 10 people who have no manager, so the two counts differ in some departments — and HAVING can test exactly that:

SELECT department,
       COUNT(*)          AS employees,
       COUNT(manager_id) AS with_manager
FROM employees
GROUP BY department
HAVING COUNT(manager_id) < COUNT(*);

▶ Run this query in the SQL Playground

These 6 departments each contain at least one employee without a manager. The other 4 have equal counts and are filtered out. The choice matters whenever a column can be NULL: HAVING COUNT(shipped_date) = 0 on orders grouped by status returns the 4 statuses in which no order has shipped, whereas HAVING COUNT(*) = 0 can never return anything, because a group always has at least one row.

HAVING with SUM()

SUM(column) adds up a numeric column inside each group; HAVING keeps the groups whose total passes the test. Which shipping cities have received more than 30,000 in orders?

SELECT shipping_city,
       ROUND(SUM(total), 2) AS total_revenue
FROM orders
GROUP BY shipping_city
HAVING SUM(total) > 30000
ORDER BY total_revenue DESC;

▶ Run this query in the SQL Playground

5 of 20 cities pass. Typical use: revenue thresholds, budget limits, customers above a spending level. Order totals in the practice database are plain numbers with no currency attached.

HAVING with AVG()

AVG(column) returns the mean of each group. Which product categories have an average price above 1,100?

SELECT category,
       ROUND(AVG(price), 2) AS average_price
FROM products
GROUP BY category
HAVING AVG(price) > 1100;

▶ Run this query in the SQL Playground

The filter uses the unrounded average; ROUND(…, 2) only tidies the displayed value. AVG ignores NULLs rather than counting them as zero. Typical use: above-average segments, quality scores, average order value.

HAVING with MIN() and MAX()

MIN and MAX turn a statement about every row, or about at least one row, into a group filter. HAVING MIN(salary) > 60000 keeps departments where everyone earns more than 60,000 — if the lowest salary clears the bar, all of them do:

SELECT department,
       MIN(salary) AS lowest_salary
FROM employees
GROUP BY department
HAVING MIN(salary) > 60000;

▶ Run this query in the SQL Playground

HAVING MAX(salary) > 200000 keeps departments where at least one person earns more than 200,000:

SELECT department,
       MAX(salary) AS highest_salary
FROM employees
GROUP BY department
HAVING MAX(salary) > 200000;

▶ Run this query in the SQL Playground

Typical use: "all items in the group meet a minimum" (MIN), "the group contains at least one large value" (MAX). MIN and MAX also work on dates and text. For what each function calculates in detail, see SQL aggregate functions.

WHERE filters individual rows before they are grouped. HAVING filters whole groups after the aggregates have been calculated. A condition about a column value goes in WHERE; a condition about a count, total or average goes in HAVING.

Using WHERE and HAVING in One Query

They are not alternatives — most real reports use both. Counting successful payments only, which payment methods collected more than 20,000?

SELECT method,
       COUNT(*)              AS payments,
       ROUND(SUM(amount), 2) AS collected
FROM payments
WHERE status = 'success'
GROUP BY method
HAVING SUM(amount) > 20000
ORDER BY collected DESC;

▶ Run this query in the SQL Playground

What happens at each stage:

1. FROM reads the 200 rows of payments.
2. WHERE keeps the 137 rows whose status is 'success'. Failed, pending and refunded payments are gone before anything is added up.
3. GROUP BY puts those rows into 8 groups, one per method, and SUM(amount) is calculated for each.
4. HAVING keeps the 3 groups whose total is above 20,000.
5. ORDER BY sorts the surviving groups, largest total first.

Why WHERE Cannot Filter an Aggregate

SELECT method, SUM(amount)
FROM payments
WHERE SUM(amount) > 20000
GROUP BY method;

SELECT method, SUM(amount)
FROM payments
GROUP BY method
HAVING SUM(amount) > 20000;

WHERE looks at one row at a time, and a single payment has no "total per method". That number only exists after grouping, which is where HAVING works.

WHERE Changes the Numbers, HAVING Does Not

Moving a condition between the two clauses changes the question. Here WHERE keeps only orders above 4,000 (47 of 200), so COUNT(*) counts big orders, and HAVING keeps cities with four or more of them:

SELECT shipping_city,
       COUNT(*) AS big_orders
FROM orders
WHERE total > 4000
GROUP BY shipping_city
HAVING COUNT(*) >= 4
ORDER BY big_orders DESC;

▶ Run this query in the SQL Playground

Remove the WHERE line and COUNT(*) counts every order, so all 20 cities pass HAVING COUNT(*) >= 4. Same HAVING, different answer — because WHERE decided what was being counted.

A Condition on the Grouped Column: WHERE or HAVING?

HAVING may also test a column that appears in GROUP BY. HAVING department IN ('Legal', 'Sales') and WHERE department IN ('Legal', 'Sales') return the same 2 rows in the Playground. Prefer WHERE: it states the intent — "only these rows" — and discards the unwanted rows before the database groups and aggregates them, instead of building groups that are then thrown away. Keep HAVING for conditions that need an aggregate.

HAVING accepts several conditions combined with AND and OR, exactly as WHERE does. Each condition can use a different aggregate, and all of them are tested against the same group.

HAVING with AND — every condition must be true

Which departments are both large (12 or more people) and well paid (average above 125,000)?

SELECT department,
       COUNT(*)           AS employees,
       ROUND(AVG(salary)) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) >= 12
   AND AVG(salary) > 125000;

▶ Run this query in the SQL Playground

5 departments have 12 or more employees, but only 2 of them also clear the salary test.

HAVING with OR — one true condition is enough

Which departments are either very large or very well paid?

SELECT department,
       COUNT(*)           AS employees,
       ROUND(AVG(salary)) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) >= 15
    OR AVG(salary) > 130000;

▶ Run this query in the SQL Playground

Legal qualifies on headcount alone; Design and Engineering qualify on salary alone.

Whether HAVING can use a column alias depends on the database. SQLite and MySQL accept an alias defined in SELECT; PostgreSQL and SQL Server do not, and require the aggregate expression to be repeated.

The Playground runs SQLite, so this works there and returns the same four departments as the first example:

SELECT department,
       COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING employee_count > 12;

▶ Run this query in the SQL Playground

The reason is the logical order of a query: HAVING is evaluated before the SELECT list, so in standard SQL the alias does not exist yet. SQLite and MySQL look the alias up anyway as a convenience.

HAVING works on joined tables exactly as it does on one table: the JOIN builds the rows, GROUP BY groups them, and HAVING filters the groups. A customer's home city lives in users and their orders in orders, so "which customer cities generated 12 or more orders?" needs both:

SELECT u.city,
       COUNT(o.id)            AS total_orders,
       ROUND(SUM(o.total), 2) AS revenue
FROM users u
JOIN orders o
    ON o.user_id = u.id
GROUP BY u.city
HAVING COUNT(o.id) >= 12
ORDER BY total_orders DESC;

▶ Run this query in the SQL Playground

1. JOIN attaches each order to the customer who placed it.
2. GROUP BY u.city builds one group per customer city.
3. HAVING COUNT(o.id) >= 12 keeps the 6 cities with enough orders.

Qualify the columns (o.id, u.city) so it is clear which table each aggregate counts. How the tables are combined is covered in SQL JOINs.

LEFT JOIN + HAVING: Finding Rows with No Match

A LEFT JOIN keeps customers who have no orders, with NULL in the order columns. Because COUNT(o.id) skips NULLs, it is 0 for exactly those customers — which makes HAVING COUNT(o.id) = 0 a way to find them:

SELECT u.id,
       u.first_name,
       u.last_name,
       COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o
    ON o.user_id = u.id
GROUP BY u.id, u.first_name, u.last_name
HAVING COUNT(o.id) = 0
ORDER BY u.id
LIMIT 5;

▶ Run this query in the SQL Playground

Without the LIMIT the query returns 36 of the 150 customers. This is the COUNT(*) vs COUNT(column) difference at work: HAVING COUNT(*) = 0 returns 0 rows here, because the LEFT JOIN still produces one row for a customer without orders. See LEFT JOIN for how unmatched rows are kept.

Yes — HAVING can be used without GROUP BY when the query uses an aggregate function. With no GROUP BY, all the rows form a single implicit group, so HAVING either keeps that one summary row or returns nothing.

SELECT COUNT(*) AS total_orders
FROM orders
HAVING COUNT(*) > 100;

▶ Run this query in the SQL Playground

Raise the threshold above the real count and the single group fails the test:

SELECT COUNT(*) AS total_orders
FROM orders
HAVING COUNT(*) > 500;

▶ Run this query in the SQL Playground

This is occasionally useful as an all-or-nothing check ("return the total only if there are enough rows"). It is not a way to filter rows.

What Differs Between Databases

• The aggregate form above runs in SQLite (version 3.39 and later, which includes the Playground), MySQL, PostgreSQL and SQL Server.
• HAVING on a plain column with no aggregate and no GROUP BY is rejected by SQLite, PostgreSQL and SQL Server. MySQL accepts it and treats it like a row filter, which is a MySQL extension.

SELECT id, total
FROM orders
HAVING total > 4900;

SELECT id, total
FROM orders
WHERE total > 4900;

Two patterns for when a fixed number is not enough.

Compare Each Group with an Overall Value

The threshold does not have to be typed in. A subquery can calculate it — here, the average salary of the whole company (124371):

SELECT department,
       ROUND(AVG(salary)) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees)
ORDER BY avg_salary DESC;

▶ Run this query in the SQL Playground

The subquery runs on all employees; the outer AVG(salary) runs once per department. More patterns like this are in SQL subqueries.

Count Only Some Rows in Each Group

A CASE expression inside an aggregate counts just the rows that match a condition, without removing the others from the group. Which cities have four or more cancelled or refunded orders — shown alongside all of their orders?

SELECT shipping_city,
       COUNT(*) AS orders,
       SUM(CASE WHEN status IN ('cancelled', 'refunded') THEN 1 ELSE 0 END) AS lost_orders
FROM orders
GROUP BY shipping_city
HAVING SUM(CASE WHEN status IN ('cancelled', 'refunded') THEN 1 ELSE 0 END) >= 4
ORDER BY lost_orders DESC;

▶ Run this query in the SQL Playground

A WHERE filter could not produce this result: it would remove the other orders, and the orders column would no longer show each city's full count. See CASE WHEN for the expression itself.

A query is written starting with SELECT, but it is logically processed in a different order. HAVING is the fourth step — after the groups exist, before the SELECT list is produced:

1. FROM / JOINRead the tables and combine them.
2. WHERERemove rows that fail the row condition.
3. GROUP BYPut the remaining rows into groups and calculate the aggregates.
4. HAVINGRemove groups that fail the group condition.
5. SELECTProduce the output columns and their aliases.
6. ORDER BYSort the result.
7. LIMITKeep the first N rows.

This order explains the rules in this lesson:

• WHERE cannot use an aggregate — step 2 happens before the groups of step 3 exist.
• HAVING can use an aggregate — by step 4 every group has its counts and totals.
• An alias is not guaranteed to work in HAVING — aliases are created in step 5. SQLite and MySQL resolve them early; PostgreSQL and SQL Server do not.
• ORDER BY can always use an alias — it runs after SELECT.

Ten errors, each with its correction.

SELECT department, COUNT(*)
FROM employees
HAVING COUNT(*) > 12;

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 12;

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING salary > 100000;

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING AVG(salary) > 100000;

Eight business questions, answered on the Playground's e-commerce data.

Each example gives the question, the query, its real output and the idea to take away. Open one in the Playground, change the threshold and run it again — the fastest way to get a feel for HAVING is to watch groups appear and disappear.

SELECT u.first_name, u.last_name,
       COUNT(o.id)            AS order_count,
       ROUND(SUM(o.total), 2) AS total_spent
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.first_name, u.last_name
HAVING COUNT(o.id) >= 4
ORDER BY order_count DESC, total_spent DESC;

SELECT name,
       COUNT(*) AS listings
FROM products
GROUP BY name
HAVING COUNT(*) > 1
ORDER BY listings DESC, name
LIMIT 5;

SELECT p.category,
       ROUND(SUM(oi.subtotal), 2) AS delivered_revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
JOIN orders o   ON o.id = oi.order_id
WHERE o.status = 'delivered'
GROUP BY p.category
HAVING SUM(oi.subtotal) > 15000
ORDER BY delivered_revenue DESC;

SELECT p.name,
       COUNT(r.id)             AS reviews,
       ROUND(AVG(r.rating), 2) AS avg_rating
FROM products p
JOIN reviews r ON r.product_id = p.id
GROUP BY p.id, p.name
HAVING COUNT(r.id) >= 3
   AND AVG(r.rating) >= 3.5
ORDER BY avg_rating DESC, reviews DESC;

SELECT u.first_name, u.last_name,
       COUNT(o.id)            AS orders,
       ROUND(SUM(o.total), 2) AS total_spent
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.first_name, u.last_name
HAVING SUM(o.total) > 11000
ORDER BY total_spent DESC;

SELECT method,
       COUNT(*) AS payments,
       SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed
FROM payments
GROUP BY method
HAVING COUNT(*) > 25
ORDER BY payments DESC;

SELECT strftime('%Y-%m', order_date) AS month,
       COUNT(*)              AS orders,
       ROUND(SUM(total), 2)  AS revenue
FROM orders
GROUP BY strftime('%Y-%m', order_date)
HAVING COUNT(*) >= 9
ORDER BY month;

SELECT shipping_city,
       COUNT(*)             AS orders,
       ROUND(AVG(total), 2) AS avg_order_value
FROM orders
GROUP BY shipping_city
HAVING COUNT(*) >= 10
   AND AVG(total) > 2600
ORDER BY avg_order_value DESC;

9 exercises, beginner to challenge, on the real Playground tables.

Write each query yourself before opening the solution. Every solution has been run against the Playground database, and the expected result tells you what you should see.

1. 1Beginner Which shipping cities have received more than 10 orders? Show the city and its order count, largest first. Schema: orders (id, shipping_city, total, status) · Concept: HAVING COUNT(*)

   Show solution

   SQLite · runs in the Playground

   SELECT shipping_city,
          COUNT(*) AS order_count
   FROM orders
   GROUP BY shipping_city
   HAVING COUNT(*) > 10
   ORDER BY order_count DESC;

   ▶ Run this query in the SQL Playground

   Expected result: 8 cities are returned. Jacksonville is first with 15 orders. A city with exactly 10 orders is excluded, because the condition is > 10, not >= 10.

2. 2Beginner Which product categories contain 12 or more products? Schema: products (id, name, category, price) · Concept: HAVING COUNT(*)

   Show solution

   SQLite · runs in the Playground

   SELECT category,
          COUNT(*) AS products
   FROM products
   GROUP BY category
   HAVING COUNT(*) >= 12
   ORDER BY products DESC;

   ▶ Run this query in the SQL Playground

   Expected result: 5 of the 10 categories qualify; the largest is Food & Beverage with 17 products.

3. 3Intermediate Which departments have a total payroll above 1,500,000? Schema: employees (id, department, salary) · Concept: HAVING SUM()

   Show solution

   SQLite · runs in the Playground

   SELECT department,
          SUM(salary) AS total_salary
   FROM employees
   GROUP BY department
   HAVING SUM(salary) > 1500000
   ORDER BY total_salary DESC;

   ▶ Run this query in the SQL Playground

   Expected result: SUM adds the salaries inside each department; 4 departments pass. Customer Success has the largest payroll (2012099).

4. 4Intermediate Looking at successful payments only, which payment methods have an average payment above 1,000? Schema: payments (id, amount, method, status) · Concept: WHERE + HAVING AVG()

   Show solution

   SQLite · runs in the Playground

   SELECT method,
          COUNT(*)              AS payments,
          ROUND(AVG(amount), 2) AS avg_payment
   FROM payments
   WHERE status = 'success'
   GROUP BY method
   HAVING AVG(amount) > 1000
   ORDER BY avg_payment DESC;

   ▶ Run this query in the SQL Playground

   Expected result: status = 'success' is a row condition, so it goes in WHERE; the average is a group condition, so it goes in HAVING. 5 methods are returned, led by Google Pay (1264.63).

5. 5Intermediate Which job titles are held by five or more employees? Show the headcount and the average salary for each. Schema: employees (id, title, salary) · Concept: Filter on COUNT, report AVG

   Show solution

   SQLite · runs in the Playground

   SELECT title,
          COUNT(*)           AS employees,
          ROUND(AVG(salary)) AS avg_salary
   FROM employees
   GROUP BY title
   HAVING COUNT(*) >= 5
   ORDER BY employees DESC;

   ▶ Run this query in the SQL Playground

   Expected result: 4 titles qualify; Senior CS Manager is the most common with 7 people. The average is displayed but not filtered on.

6. 6Intermediate Which products have sold 25 or more units in total? Show the product id and units sold. Schema: order_items (id, order_id, product_id, quantity, subtotal) · Concept: HAVING SUM() on a quantity

   Show solution

   SQLite · runs in the Playground

   SELECT product_id,
          SUM(quantity) AS units_sold
   FROM order_items
   GROUP BY product_id
   HAVING SUM(quantity) >= 25
   ORDER BY units_sold DESC;

   ▶ Run this query in the SQL Playground

   Expected result: Units are a SUM of quantity, not a COUNT of rows — one order line can contain several units. 7 products qualify; product 38 leads with 37 units.

7. 7Advanced Which managers have 12 or more direct reports? Show each manager's name and number of reports. Schema: employees (id, first_name, last_name, manager_id) · Concept: Self join + HAVING

   Show solution

   SQLite · runs in the Playground

   SELECT m.first_name, m.last_name,
          COUNT(e.id) AS direct_reports
   FROM employees e
   JOIN employees m ON m.id = e.manager_id
   GROUP BY m.id, m.first_name, m.last_name
   HAVING COUNT(e.id) >= 12
   ORDER BY direct_reports DESC;

   ▶ Run this query in the SQL Playground

   Expected result: The table is joined to itself: e is the employee, m is that employee's manager. 5 managers qualify; Michael Campbell has the most reports (14). The pattern is explained in SELF JOIN.

8. 8Advanced Which customers have paid with three or more different payment methods? Schema: payments (id, user_id, method) · users (id, first_name, last_name) · Concept: HAVING COUNT(DISTINCT …)

   Show solution

   SQLite · runs in the Playground

   SELECT u.first_name, u.last_name,
          COUNT(DISTINCT p.method) AS methods_used
   FROM users u
   JOIN payments p ON p.user_id = u.id
   GROUP BY u.id, u.first_name, u.last_name
   HAVING COUNT(DISTINCT p.method) >= 3
   ORDER BY u.last_name, u.first_name;

   ▶ Run this query in the SQL Playground

   Expected result: COUNT(DISTINCT method) counts each method once per customer, however many times it was used. 11 customers are returned. DISTINCT inside an aggregate is what separates "three payments" from "three different methods".

9. 9Challenge Which customer home cities have at least 10 orders and an average order value above the average of all orders? Schema: users (id, city) · orders (id, user_id, total) · Concept: JOIN + two conditions + subquery

   Show solution

   SQLite · runs in the Playground

   SELECT u.city,
          COUNT(o.id)            AS orders,
          ROUND(AVG(o.total), 2) AS avg_order_value
   FROM users u
   JOIN orders o ON o.user_id = u.id
   GROUP BY u.city
   HAVING COUNT(o.id) >= 10
      AND AVG(o.total) > (SELECT AVG(total) FROM orders)
   ORDER BY avg_order_value DESC;

   ▶ Run this query in the SQL Playground

   Expected result: The subquery returns one number — the overall average order value — and each city's average is compared with it. 6 cities pass both tests; Phoenix is highest at 3343.32.

Six questions that come up often, with concise answers.

More practice across every topic: SQL interview questions and answers.

Ten patterns for quick reference.

Complete Query Template

SELECT   column_name,              -- the group
         aggregate_fn(column)      -- the metric
FROM     table_name
WHERE    row_condition             -- filter rows (no aggregates)
GROUP BY column_name               -- build the groups
HAVING   aggregate_fn(column) > 0  -- filter groups (aggregates allowed)
ORDER BY column_name               -- sort the result
LIMIT    10;                       -- keep the first rows