The HAVING clause in SQL is used to filter aggregated data after grouping. It works with aggregate functions like SUM(), COUNT(), AVG(), MIN(), MAX().
- Used after GROUP BY
- Filters groups, not individual rows
- WHERE → filters rows before grouping
- HAVING → filters results after aggregation
- Ideal for totals, averages, or other summary conditions
Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY expression
HAVING condition
ORDER BY expression
LIMIT value;Key Terms
- GROUP BY: Groups rows based on the specified column or expression.
- HAVING: Filters the grouped results using conditions on aggregate functions.
- ORDER BY: Orders the final result set.
Example 1: Using SUM() with HAVING Clause
This example shows how to find products whose total cost is greater than a specified value. To do this, we use the SUM() function with the HAVING clause. So, Lets Create the products Table using following query.
Query:
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(45),
product_cost FLOAT
);
INSERT INTO products VALUES
(1001, 'Colgate Toothpaste', 2.25),
(1002, 'T-Shirt', 5),
(1003, 'Jeans', 6.5),
(1004, 'Shorts', 4.5),
(1005, 'Sneakers', 8.99),
(1006, 'Mouthwash', 3.35),
(1007, 'Denim Jeans', 8.99),
(1008, 'Synsodyne Toothpaste', 3.35);Output:
Find Products with Total Cost Greater Than 3.5
This query groups products by name and cost, calculates the total cost for each group using SUM(product_cost), and filters groups where the total cost exceeds 3.5. The results are then sorted by product cost in ascending order using ORDER BY.
Query:
SELECT product_name, product_cost
FROM products
GROUP BY product_name, product_cost
HAVING SUM(product_cost) > 3.5
ORDER BY product_cost;Output
Example 2: Using MAX() and MIN() with HAVING Clause
This example demonstrates how to use the MAX() and MIN() functions with the HAVING clause to filter products based on their highest and lowest prices.
Find products with a maximum price greater than 7
This query groups products by name, calculates the maximum price for each group, and filters those with a maximum price greater than 7.
Query:
SELECT product_name
FROM products
GROUP BY product_name
HAVING MAX(product_cost) > 7;Output
Find Products with a Minimum Price Less Than 3
This query groups products by their name, calculates the minimum price for each group using MIN(product_cost) and filters the groups where the minimum price is less than 3.
Query:
SELECT product_name
FROM products
GROUP BY product_name
HAVING MIN(product_cost) < 3;Output:
Example 3: Using AVG() with HAVING Clause
This example demonstrates how to filter products based on whether their price exceeds the average price of all products in the table.
Select products whose price is greater than the average price of all products.
This query calculates the average price for each product group using AVG(product_cost) and compares it with the overall average price obtained from a subquery. It filters and returns only those products whose group average price is greater than the overall average.
Query:
SELECT product_name
FROM products
GROUP BY product_name
HAVING AVG(product_cost) > (SELECT AVG(product_cost) FROM products);Output:
Example 4: Using COUNT() with HAVING Clause
This example demonstrates how to count the number of occurrences of customer IDs and filter those that appear at least twice.
Query:
CREATE TABLE login(signin_id int PRIMARY KEY ,customer_id int, date_login date);
INSERT INTO login values
(1, 121, '2021-10-21'), (2, 135, '2021-05-25'),
(3, 314, '2021-03-13'), (4, 245, '2021-07-19'),
(5, 672, '2021-09-23'), (6, 135, '2021-06-12'),
(7,120,'2021-06-14'), (8, 121, '2021-04-24'),
(9,135, '2021-06-15'), (10, 984, '2021-01-30');
SELECT * FROM login;Output:
Display customer IDs that occurred at least 2 times
This query groups the data by customer_id, counts the number of occurrences for each group using COUNT(customer_id), and filters groups where the count is greater than or equal to 2.
Query:
SELECT customer_id
FROM login
GROUP BY customer_id
HAVING COUNT(customer_id) >=2 ;Output:
