HAVING Clause
In this page:
SELECT column_name, aggregate_function(column)
FROM table_name
GROUP BY column_name
HAVING aggregate_condition;Filtering Groups
The HAVING clause filters grouped data after aggregation has already happened. Because the WHERE clause cannot evaluate aggregate functions like COUNT() or SUM(), you must use HAVING to filter groups based on those aggregate results.
Example: Filtering Groups
CREATE TABLE orders (id INT, customer_id INT);
INSERT INTO orders VALUES (1, 1), (2, 1), (3, 2);
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;HAVING vs WHERE
Remember the key difference: WHERE filters individual rows before they are grouped, while HAVING filters the summarized groups after they are processed. Mixing these up is one of the most common SQL mistakes beginners make.
Example: HAVING vs WHERE
CREATE TABLE orders (id INT, region TEXT, total INT);
INSERT INTO orders VALUES (1, 'North', 500), (2, 'North', 50), (3, 'South', 800);
SELECT region, SUM(total) AS region_total
FROM orders
WHERE total > 0
GROUP BY region
HAVING SUM(total) > 300;HAVING with Multiple Conditions
You can combine multiple group filters in a HAVING clause just like you would in WHERE. Use logical operators like AND and OR to connect your rules, such as requiring both a minimum order count and a minimum total spend.
Example: HAVING with Multiple Conditions
CREATE TABLE orders (id INT, customer_id INT, total INT);
INSERT INTO orders VALUES (1, 1, 100), (2, 1, 200), (3, 2, 50);
SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS spend
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2 AND SUM(total) > 200;HAVING with Alias Values
MySQL allows you to reference alias column names defined in your SELECT clause inside the HAVING filter, unlike standard WHERE clauses in most databases. This keeps your queries readable by letting you reuse the same name instead of repeating the full aggregate expression.
Example: HAVING with Alias Values
CREATE TABLE orders (id INT, category TEXT, total INT);
INSERT INTO orders VALUES (1, 'Books', 50), (2, 'Books', 60), (3, 'Toys', 10);
SELECT category, SUM(total) AS category_total
FROM orders
GROUP BY category
HAVING category_total > 100;Complex Group Limits
HAVING can handle complex mathematical comparisons on aggregated values. It is excellent for identifying deviations and trends in your data, such as flagging categories whose average price jumped by more than a set threshold.
Example: Complex Group Limits
CREATE TABLE prices (id INT, category TEXT, price INT);
INSERT INTO prices VALUES (1, 'Books', 10), (2, 'Books', 100), (3, 'Toys', 20);
SELECT category, AVG(price) AS avg_price
FROM prices
GROUP BY category
HAVING AVG(price) > 50;- Using
WHERE COUNT(*) > 1, which is invalid, when the filter on aggregates belongs inHAVING. - Putting row filters in
HAVINGinstead ofWHERE, making the query slower. - Writing
HAVINGbeforeGROUP BY, which is a syntax error.
- Aggregate functions such as
COUNT(),SUM(),AVG(),MIN(), andMAX()summarize many rows into one value. GROUP BYgroups rows so aggregates can be calculated per group.HAVINGfilters groups after aggregation.
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: