MySQL HAVING Clause

The HAVING clause in MySQL filters groups after the GROUP BY operation has been applied. It is used to set conditions on aggregate functions — something the WHERE clause cannot do, since WHERE runs before grouping takes place.

WHERE vs HAVING

Syntax

SELECT column_name, aggregate_function(column)
FROM table_name
GROUP BY column_name
HAVING condition;

Sample Table

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    salesperson VARCHAR(100),
    region VARCHAR(50),
    amount DECIMAL(10,2)
);

INSERT INTO sales VALUES (1, 'Arjun', 'North', 15000);
INSERT INTO sales VALUES (2, 'Priya', 'South', 22000);
INSERT INTO sales VALUES (3, 'Arjun', 'North', 18000);
INSERT INTO sales VALUES (4, 'Meena', 'East',   9500);
INSERT INTO sales VALUES (5, 'Priya', 'South', 31000);
INSERT INTO sales VALUES (6, 'Meena', 'East',  12000);

Example: Show Regions with Total Sales Above 30000

SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region
HAVING SUM(amount) > 30000;

Result:

region | total_sales
-------+------------
North  | 33000.00
South  | 53000.00

East had total sales of 21500, which is below 30000, so it is excluded.

Example: Show Salespersons with More Than 1 Sale

SELECT salesperson, COUNT(*) AS total_transactions
FROM sales
GROUP BY salesperson
HAVING COUNT(*) > 1;

Result:

salesperson | total_transactions
------------+-------------------
Arjun       | 2
Priya       | 2
Meena       | 2

Example: Using HAVING with AVG

SELECT region, AVG(amount) AS avg_sale
FROM sales
GROUP BY region
HAVING AVG(amount) >= 15000;

Using WHERE and HAVING Together

WHERE filters individual rows first, then GROUP BY groups the remaining rows, and finally HAVING filters those groups.

SELECT salesperson, SUM(amount) AS total
FROM sales
WHERE region != 'East'
GROUP BY salesperson
HAVING SUM(amount) > 25000;

This query:

1. Excludes all East region rows (WHERE)
2. Groups remaining rows by salesperson (GROUP BY)
3. Keeps only salespersons with total sales above 25000 (HAVING)

HAVING with ORDER BY

SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region
HAVING SUM(amount) > 20000
ORDER BY total_sales DESC;

Key Points

• HAVING filters groups created by GROUP BY.
• It is the only clause that can filter on aggregate functions like SUM(), COUNT(), AVG().
• WHERE filters rows before grouping; HAVING filters groups after grouping.
• Both WHERE and HAVING can be used in the same query.
• HAVING is always placed after GROUP BY and before ORDER BY.