MySQL HAVING Clause: Filter Groups After GROUP BY

WHERE filters rows before they are grouped. HAVING filters groups after the aggregates have been calculated.

query.sql

SQL

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    ordered_on DATE NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL
);

INSERT INTO customers (name, city) VALUES
    ('Maya Shah',    'Pune'),
    ('Rohan Verma',  'Delhi'),
    ('Sana Ali',     'Pune'),
    ('Tara Nair',    'Kochi'),
    ('Uday Singh',   'Delhi');

INSERT INTO orders (customer_id, ordered_on, total, status) VALUES
    (1, '2026-05-02',  680.00, 'delivered'),
    (1, '2026-05-20', 1299.00, 'delivered'),
    (2, '2026-05-21',  145.00, 'shipped'),
    (3, '2026-06-01',  450.00, 'delivered'),
    (3, '2026-06-05',   80.00, 'cancelled'),
    (3, '2026-06-18',  999.00, 'shipped'),
    (2, '2026-06-20',  220.00, 'pending');

Groups that meet a condition

“Which customers have placed more than one order?”

query.sql

SQL

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

Output

OUTPUT

+-------------+--------+---------+
| customer_id | orders | spent   |
+-------------+--------+---------+
|           1 |      2 | 1979.00 |
|           2 |      2 |  365.00 |
|           3 |      3 | 1529.00 |
+-------------+--------+---------+
3 rows in set

You cannot write WHERE COUNT(*) > 1, because WHERE runs before the groups exist:

query.sql

SQL

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

Output

OUTPUT

ERROR 1111 (HY000): Invalid use of group function

WHERE and HAVING together

Use WHERE for conditions on rows, and HAVING for conditions on aggregates:

query.sql

SQL

SELECT customer_id, COUNT(*) AS delivered_orders, SUM(total) AS spent
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
HAVING SUM(total) > 1000
ORDER BY spent DESC;

Output

OUTPUT

+-------------+------------------+---------+
| customer_id | delivered_orders | spent   |
+-------------+------------------+---------+
|           1 |                2 | 1979.00 |
+-------------+------------------+---------+
1 row in set

Step by step: keep only delivered orders (WHERE), group them per customer, calculate the sum, keep only customers whose sum is above 1000 (HAVING).

Put every condition you can in WHERE. It removes rows early, so the groups are smaller and the query is faster. This pair gives the same answer, but the second is better:

query.sql

SQL

1
2

SELECT status, COUNT(*) AS orders FROM orders GROUP BY status HAVING status = 'delivered';
SELECT status, COUNT(*) AS orders FROM orders WHERE status = 'delivered' GROUP BY status;

Output

OUTPUT

+-----------+--------+
| status    | orders |
+-----------+--------+
| delivered |      3 |
+-----------+--------+
1 row in set

+-----------+--------+
| status    | orders |
+-----------+--------+
| delivered |      3 |
+-----------+--------+
1 row in set

HAVING with an alias

MySQL allows an alias from the SELECT list in HAVING (standard SQL does not):

query.sql

SQL

SELECT customer_id, SUM(total) AS spent FROM orders GROUP BY customer_id HAVING spent > 500 ORDER BY spent DESC;

Output

OUTPUT

+-------------+---------+
| customer_id | spent   |
+-------------+---------+
|           1 | 1979.00 |
|           3 | 1529.00 |
+-------------+---------+
2 rows in set

HAVING without GROUP BY

Without GROUP BY, the whole table is one group, and HAVING tests the totals:

query.sql

SQL

SELECT COUNT(*) AS orders, SUM(total) AS revenue FROM orders HAVING SUM(total) > 100000;

Output

OUTPUT

Empty set

An empty result tells you that the revenue is not above 100,000. This is rare, but it explains why HAVING can exist without GROUP BY.

Finding duplicates

The most useful HAVING pattern: find values that appear more than once.

query.sql

SQL

CREATE TABLE contacts (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(30), city VARCHAR(20));
INSERT INTO contacts (email, city) VALUES ('a@example.com', 'Pune'), ('b@example.com', 'Delhi'), ('a@example.com', 'Kochi'), ('c@example.com', 'Pune'), ('b@example.com', 'Delhi'), ('a@example.com', 'Pune');

SELECT email, COUNT(*) AS copies, GROUP_CONCAT(id ORDER BY id) AS ids
FROM contacts
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY email;

Output

OUTPUT

+---------------+--------+-------+
| email         | copies | ids   |
+---------------+--------+-------+
| a@example.com |      3 | 1,3,6 |
| b@example.com |      2 | 2,5   |
+---------------+--------+-------+
2 rows in set

To check duplicates over several columns, group by them all (GROUP BY email, city). After cleaning, prevent them with a unique index.

Conditions with several aggregates

query.sql

SQL

SELECT customer_id, COUNT(*) AS orders, MAX(total) AS biggest, MIN(total) AS smallest
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2 AND MAX(total) > 500
ORDER BY customer_id;

Output

OUTPUT

+-------------+--------+---------+----------+
| customer_id | orders | biggest | smallest |
+-------------+--------+---------+----------+
|           1 |      2 | 1299.00 |   680.00 |
|           3 |      3 |  999.00 |    80.00 |
+-------------+--------+---------+----------+
2 rows in set

Rules to remember

HAVING can use aggregates, grouped columns and (in MySQL) aliases.
A HAVING condition that uses no aggregate usually belongs in WHERE.
HAVING runs after grouping, so it cannot use an index to skip groups. Filter early with WHERE.

lightbulb

The sentence test

If the condition says “groups with …” or “customers who have more than …”, it is HAVING. If it says “orders that are …”, it is WHERE.