SQL HAVING

The HAVING clause in SQL is used to filter records based on aggregated results. It works with GROUP BY to specify conditions on grouped data, unlike the WHERE clause, which filters individual rows before aggregation.

 

Syntax of SQL HAVING

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

• column_name: The column(s) being grouped.
• aggregate_function(): A function like SUM(), COUNT(), AVG(), MAX(), or MIN().
• table_name: The table to query data from.
• condition: The filter condition applied to the grouped data.

 

Key Points

• The HAVING clause filters grouped data, while WHERE filters individual rows before aggregation.
• It is used only with aggregate functions.
• It always comes after the GROUP BY clause but before the ORDER BY clause.

 

Example of SQL HAVING

Consider the following table:

Table: Orders

To find customers who ordered more than 5 items in total:

SELECT customer_name, SUM(quantity) AS total_quantity  
FROM orders  
GROUP BY customer_name  
HAVING SUM(quantity) > 5;  

Output:

customer_name	total_quantity
Alice	8
Charlie	7

Explanation:

• GROUP BY customer_name groups the rows by customer_name.
• SUM(quantity) AS total_quantity calculates the total quantity for each customer.
• HAVING SUM(quantity) > 5 filters out groups where the total quantity is 5 or less.

 

SQL HAVING vs. WHERE

Feature	WHERE Clause	HAVING Clause
Applied on	Individual rows	Grouped records
Works with Aggregate Functions?	No	Yes
Execution Order	Before GROUP BY	After GROUP BY

Example: Find orders where individual quantity is greater than 3 (using WHERE)

SELECT *  
FROM orders  
WHERE quantity > 3;  

Example: Find customers who ordered more than 3 items in total (using HAVING)

SELECT customer_name, SUM(quantity) AS total_quantity  
FROM orders  
GROUP BY customer_name  
HAVING SUM(quantity) > 3;  

 

SQL HAVING with Multiple Conditions

You can use multiple conditions with HAVING using logical operators (AND, OR).

Example: Find products with a total quantity greater than 5 and an average quantity greater than 2:

SELECT product_name, SUM(quantity) AS total_quantity, AVG(quantity) AS avg_quantity  
FROM orders  
GROUP BY product_name  
HAVING SUM(quantity) > 5 AND AVG(quantity) > 2;  

 

SQL HAVING with COUNT()

Example: Find customers who placed more than 1 order:

SELECT customer_name, COUNT(order_id) AS order_count  
FROM orders  
GROUP BY customer_name  
HAVING COUNT(order_id) > 1;  

Output:

customer_name	order_count
Alice	2
Bob	2

 

Summary

• HAVING is used to filter grouped data after applying aggregate functions.
• It comes after GROUP BY and works only with aggregate functions.
• Use WHERE for filtering individual rows and HAVING for filtering aggregated results.
• You can apply multiple conditions using AND and OR.