HAVING Clause in SQL

The HAVING clause in SQL is used to filter data after it has been grouped. It works closely with aggregate functions like COUNT, SUM, AVG, MAX, and MIN to filter groups based on specific conditions. Unlike the WHERE clause, which filters rows before grouping, HAVING applies conditions to the grouped results. This is helpful when you want to show only groups that meet specific conditions, like products with total sales above a certain amount. In this blog, you will explore the syntax, use cases, and best practices for using the HAVING clause in SQL effectively.

Table of Contents:

• What is the HAVING Clause in SQL?
• Syntax of the HAVING Clause in SQL
• Using the HAVING Clause with GROUP BY in SQL
• Using the HAVING Clause with ORDER BY in SQL
• Using Aggregate Functions with the HAVING Clause in SQL
• Difference Between HAVING Clause and WHERE Clause in SQL
• Common Mistakes When Using the HAVING Clause in SQL
• Best Practices for Using the HAVING Clause in SQL
• Conclusion

What is the HAVING Clause in SQL?

The HAVING clause in SQL is used to filter grouped data after aggregate functions, such as SUM, AVG, COUNT, MIN, and MAX have been applied. It is especially useful when applying conditions to the results of a GROUP BY clause.

Key Features of the HAVING Clause in SQL:

1. Works with aggregate functions: It allows you to filter groups based on the results of aggregate functions such as SUM, AVG, COUNT, MAX, and MIN.
2. Used with GROUP BY: The HAVING clause is commonly used with GROUP BY because it filters data after it has been grouped.
3. Filters grouped data: The WHERE clause filters individual rows before grouping, while the HAVING clause filters entire groups after aggregation.
4. Not a replacement for WHERE: HAVING cannot be used to filter individual rows before grouping. It is specifically meant for filtering after the grouping has occurred.
5. Supports multiple conditions: Like the WHERE clause, HAVING can include multiple conditions using logical operators such as AND, OR, and NOT.

Master SQL and Supercharge Your Data Skills

Learn advanced queries, database design, and optimization. Get certified and job-ready.

Explore Program

Syntax of the HAVING Clause in SQL

Let’s look at the syntax of the HAVING clause in SQL:

Syntax:

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

Explanation: 

• The SELECT statement is used to choose the column that you want to display from the table.
• AGGREGATE_FUNCTION(column_name) is used to apply functions like SUM(), AVG(), COUNT(), and others on column values in a table.
• FROM table_name is used to tell SQL which table to use.
• GROUP BY column_name is used to group the rows that have the same value in a specific column.
• HAVING condition is used to filter the results, which are grouped based on a condition.

NOTE: Let’s create a sample table that we will be using throughout this blog to understand the working of the HAVING Clause in SQL.

-- Creating the sales table
CREATE TABLE sales (
    id INT,
    product_name VARCHAR(50),
    quantity INT,
    price_per_unit DECIMAL(10, 2),
    region VARCHAR(50)
);
-- Inserting sample data
INSERT INTO sales (id, product_name, quantity, price_per_unit, region) VALUES
(1, 'Pen', 10, 5.00, 'North'),
(2, 'Notebook', 5, 20.00, 'South'),
(3, 'Pen', 20, 5.00, 'North'),
(4, 'Pencil', 30, 2.00, 'East'),
(5, 'Notebook', 15, 20.00, 'South'),
(6, 'Eraser', 50, 1.50, 'West'),
(7, 'Pen', 25, 5.00, 'North');
-- Display the result
SELECT * FROM sales;

Output: 

This is how the table looks after creating and inserting the table.

Using the HAVING Clause with GROUP BY in SQL

The HAVING clause in SQL with GROUP BY is used to group data by a specific column. The HAVING clause is used to filter the grouped results based on a condition.

Example: Show all the products in the sales table with a total quantity of more than 30.

SELECT product_name, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_name
HAVING SUM(quantity) > 30;

Output:

Explanation: Here, this query is used to group sales by the product name, calculate the total quantity for each product, and also filter only those with a total quantity greater than 30.

Using the HAVING Clause with ORDER BY in SQL

The HAVING clause in SQL with ORDER BY is used to sort the final result. When it is used with HAVING, it helps to display the filtered group data in a specific order.

Example: List products in descending order with the total quantity above 20.

SELECT product_name, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_name
HAVING SUM(quantity) > 20
ORDER BY total_quantity DESC;

Output:

Explanation: Here, this query is used to group products by name, filtered to keep only those with a quantity more than 20 using HAVING, and sorted using ORDER BY by total quantity.

Using Aggregate Functions with the HAVING Clause in SQL

Aggregate functions in SQL, like SUM(), AVG(), and COUNT(), are used to calculate values from multiple rows. The HAVING clause is used to filter these results after grouping. This helps in showing only those groups that meet a specific calculated condition.

Let’s see the working of different aggregate functions with the HAVING clause:

Using the HAVING Clause with the COUNT() Function in SQL

The COUNT() function is a type of aggregate function that is used to count the number of rows in each group. HAVING clause is used to filter groups based on how many records they have.

Example: Display the products that were sold in more than 2 transactions.

SELECT product_name, COUNT(*) AS total_sales
FROM sales
GROUP BY product_name
HAVING COUNT(*) > 2;

Output:

Explanation: Here, this query is used to retrieve the product that was sold in more than two transactions.

Using the HAVING Clause with AVG() Function in SQL

The AVG() function is used to calculate the average value of a column. Here, the HAVING clause is used to help in filtering the groups that meet a certain condition.

Example: Display the region where the average price is more than five.

SELECT region, AVG(price_per_unit) AS avg_price
FROM sales
GROUP BY region
HAVING AVG(price_per_unit) > 5;

Output:

Explanation: Here, this query fetches the region where the average price is more than five.

Using the HAVING Clause with the MAX() Function in SQL

The MAX() is a type of aggregate function that is used to return the highest value in the group. You can use the HAVING clause to filter the groups based on their maximum values.

Example: Display the products where the quantity that is maximum is greater than 20.

SELECT product_name, MAX(quantity) AS max_order
FROM sales
GROUP BY product_name
HAVING MAX(quantity) > 20;

Output:

Explanation: Here, this query is used to fetch products where the quantity is greater than 20.

Using the HAVING Clause with the MIN() Function in SQL

The MIN() function gives the smallest value in each group. The HAVING clause is used to filter the groups that have a minimum value.

Example: Show regions where the smallest price per unit is less than 3.

SELECT region, MIN(price_per_unit) AS min_price
FROM sales
GROUP BY region
HAVING MIN(price_per_unit) < 3;

Output:

Explanation: Here, this query is used to fetch the region having a minimum price per unit of less than 3.

Using the HAVING Clause with Subqueries in SQL

Subqueries can be used inside a HAVING clause to compare group results with changing values.

Example: Display the product where the total quantity is higher than the average quantity of all products.

SELECT product_name, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_name
HAVING SUM(quantity) > (
    SELECT AVG(quantity) FROM sales
);

Output:

Example: Here, this query is used to return those whose total quantity sold is greater than the average quantity of all individual sales.

Difference Between HAVING Clause and WHERE Clause in SQL

Common Mistakes When Using the HAVING Clause in SQL

Let’s look at the common mistakes that are often made when using the HAVING Clause in SQL:

1. Using HAVING Without GROUP BY: The HAVING clause is used for filtering on results that are aggregated. Without GROUP BY, it only makes sense if you’re using it to filter the entire table’s aggregated result.

2. Using WHERE Instead of HAVING for Aggregates: You cannot use the WHERE clause to filter the results of aggregate functions like SUM() or AVG(). That’s exactly what the HAVING clause is designed for.

3. Referring to Columns that are Not Aggregated: In HAVING, if you refer to columns that are not part of the GROUP BY or not part of the aggregate function, it can either result in an error or unexpected results.

4. Not Knowing/Understanding the Order of Execution: SQL executes WHERE before GROUP BY, and it executes HAVING after GROUP BY. When combining those sequences, this can cause bad logic and/or performance.

5. Over-Complicating Conditions: If you have too many aggregate filters in HAVING, this can cause your logic to be hard to read and can impact performance.

Best Practices for Using the HAVING Clause in SQL

Let’s look at some best practices that people can follow to get the desired results:

1. Use WHERE for Pre-Aggregation Filtering: Filter rows before aggregation to reduce workload and improve query performance.

2. Utilize HAVING only for aggregate conditions: Use HAVING to focus on the results of aggregate functions only (COUNT, SUM, etc.).

3. Keep it readable: Use aliases, and format your queries legibly, especially when you are conditionally filtering using multiple conditions in HAVING.

4. Avoid Repetition: If you are going to use the same aggregate function in multiple places in the queries, consider a subquery or CTE to avoid recalculating the aggregate value.

5. Test with Sample Data: Try your query with test cases to validate the logic, especially when using GROUP BY, HAVING, or subqueries together.

Master SQL and Supercharge Your Data Skills

Learn advanced queries, database design, and optimization. Get certified and job-ready.

Explore Program

Conclusion

The HAVING clause in SQL is a powerful tool for filtering grouped data using aggregate functions like SUM, COUNT, or AVG. While it works similarly to the WHERE clause, it is applied after the GROUP BY step, allowing you to filter summary results. Using the WHERE clause to filter rows before grouping helps improve performance and keeps your queries simple and efficient. By understanding when and how to use the HAVING clause in SQL correctly, you can write more precise and efficient SQL queries that handle complex reporting and real-world data analysis with ease.

Take your skills to the next level by enrolling in the SQL Course today and gaining hands-on experience. Also, prepare for job interviews with SQL Interview Questions prepared by industry experts.

HAVING Clause in SQL – FAQs