How to Use HAVING With Aggregate Functions in SQL?

The SQL HAVING clause is an essential tool for filtering aggregated data after performing group operations. While the WHERE clause filters data before grouping, HAVING clause allows us to apply conditions to the results of aggregate functions, such as SUM(), COUNT(), AVG(), MIN() and MAX.

Using HAVING Clause in SQL to Filter Aggregate Results

HAVING is used after the GROUP BY clause to apply conditions to groups of data. WHERE works on individual rows before any aggregation, while HAVING works on the results of the aggregation. This makes it ideal for complex queries where we need to filter based on calculated totals or averages. It also helps you narrow down your results by applying conditions to the summary data rather than the individual rows.

Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY expression
HAVING condition
ORDER BY expression
LIMIT value;

Key Terms

1. GROUP BY: Groups rows based on the specified column or expression.
   
2. HAVING: Filters the grouped results using conditions on aggregate functions.
   
3. 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:

Conclusion

The HAVING clause is an indispensable tool for filtering grouped data in SQL. By allowing conditions on aggregate functions, it extends the capabilities of the GROUP BY clause and helps refine query results. Whether we’re summing costs, calculating averages, or counting occurrences, the HAVING clause enables precise and powerful data analysis. By mastering the HAVING clause, we can unlock new possibilities in data management and analysis.