How to Use HAVING With Aggregate Functions in SQL?

Q: How to use HAVING with aggregate functions in SQL?

Use HAVING to filter grouped data based on aggregate functions like SUM() or COUNT().

Q: When using aggregate functions, the HAVING clause?

HAVING filters groups after aggregation, unlike WHERE, which filters rows before grouping.

Q: Can we use AVG in HAVING clause?

Yes, AVG() can be used in HAVING to filter groups by their average values

Last Updated : 30 Dec, 2024

The HAVING clause in SQL is a powerful tool used to filter grouped data based on conditions applied to aggregate functions. While the WHERE clause is used to filter rows before grouping, the HAVING clause filters aggregated results after grouping. This makes it essential for queries involving calculations like SUM(), COUNT(), AVG(), MIN(), and MAX().

In this article, we will explore the HAVING clause in detail, learn when to use it, and understand its syntax through practical examples with different aggregate functions.

When to use the HAVING keyword?

The WHERE keyword that we used to filter data on the given condition works well with SQL operators like arithmetic, comparison, etc but when it comes to aggregate functions, HAVING keyword is used. The GROUP is also used with the HAVING keyword.

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 demonstrates 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

PRODUCTS TABLE

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.

FAQs