MySQL HAVING Clause: Filter Groups with Examples

Use the MySQL HAVING clause to keep or remove groups after a query groups rows. It is commonly used with GROUP BY and aggregate functions such as COUNT() and SUM().

Use WHERE to filter individual rows before grouping, and HAVING to filter the resulting groups. For grouping syntax and examples, see MySQL GROUP BY.

MySQL HAVING syntax

Place HAVING after GROUP BY and before ORDER BY or LIMIT:

SELECT group_column, aggregate_function(value_column) AS aggregate_alias
FROM table_name
[WHERE row_condition]
GROUP BY group_column
HAVING group_condition
ORDER BY aggregate_alias;

The WHERE clause is optional. In the HAVING clause, a condition can use a grouping column or an aggregate expression. MySQL also allows a HAVING condition to refer to an alias from the SELECT list.

Filter grouped rows with HAVING

The following example creates a small having_demo_orders table and keeps customers with at least two paid orders totaling 180 or more. Run the setup in a test database:

CREATE TABLE having_demo_orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(20) NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO having_demo_orders (order_id, customer_id, status, amount) VALUES
    (1, 101, 'paid', 120.00),
    (2, 101, 'paid', 95.00),
    (3, 101, 'cancelled', 300.00),
    (4, 102, 'paid', 100.00),
    (5, 102, 'paid', 90.00),
    (6, 103, 'paid', 80.00),
    (7, 103, 'paid', 60.00),
    (8, 104, 'pending', 1000.00);

SELECT
    customer_id,
    COUNT(*) AS paid_orders,
    SUM(amount) AS total_amount
FROM having_demo_orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) >= 2 AND SUM(amount) >= 180
ORDER BY total_amount DESC;

The query returns:

+-------------+-------------+--------------+
| customer_id | paid_orders | total_amount |
+-------------+-------------+--------------+
|         101 |           2 |       215.00 |
|         102 |           2 |       190.00 |
+-------------+-------------+--------------+

First, WHERE status = 'paid' removes rows that are not paid orders. GROUP BY customer_id then creates one group for each customer. Finally, HAVING keeps only groups that meet both aggregate conditions.

WHERE vs. HAVING

Choose the clause based on what you want to filter:

Aggregate functions such as SUM() and COUNT() cannot be used in WHERE, because the groups have not been calculated there. Put aggregate conditions in HAVING. For ordinary row conditions, use WHERE; MySQL does not optimize HAVING as a replacement for WHERE.

Use a SELECT alias in HAVING

MySQL permits a HAVING clause to use an alias defined in the SELECT list:

SELECT customer_id, SUM(amount) AS total_amount
FROM having_demo_orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING total_amount >= 180;

Referencing total_amount in HAVING is a MySQL extension. For portable SQL, use HAVING SUM(amount) >= 180 instead.

An alias cannot be used in WHERE; for example, WHERE total_amount >= 180 is not valid in the query above.

Use HAVING without GROUP BY

MySQL permits HAVING in a query without GROUP BY. If the query uses aggregates, MySQL treats the filtered input rows as one group, so HAVING can decide whether to return that aggregate result. For ordinary conditions on individual rows, use WHERE instead.

With MySQL’s default ONLY_FULL_GROUP_BY mode, nonaggregate columns referenced in the SELECT list, HAVING, or ORDER BY must be grouped or functionally dependent on the grouped columns. See MySQL handling of GROUP BY for details.

Summary

• Use WHERE to filter rows before grouping.
• Use HAVING to filter groups, especially with aggregate conditions.
• In MySQL, HAVING can refer to an alias in the SELECT list; this is not portable SQL.

For the full clause syntax and rules, see the MySQL 8.0 Reference Manual: SELECT Statement.