HAVING Clause in SQL

The HAVING clause in SQL is used to filter groups of rows based on specified conditions. It is often used with the GROUP BY clause to filter groups after aggregate functions have been applied. Unlike the WHERE clause, which filters rows before grouping, the HAVING clause filters groups after grouping and aggregation.

Key Points

1. Filtering Groups: HAVING is used to filter groups of rows based on conditions.
2. Use with GROUP BY: It is typically used with the GROUP BY clause to filter groups after aggregation.
3. Aggregate Functions: Conditions in the HAVING clause often involve aggregate functions like COUNT, SUM, AVG, MIN, and MAX.
4. Order of Execution: HAVING is executed after the GROUP BY clause and before the ORDER BY clause.

Syntax

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

• column1: The column(s) by which to group the rows.
• aggregate_function(column2): The aggregate function to apply to each group.
• table_name: The table from which to retrieve data.
• condition: The condition to filter groups.

Examples

HAVING with COUNT

Suppose you have a table named Employees that stores employee details.

Table: Employees

EmployeeID	Name	City	State	DepartmentID
1	Anand	Chennai	Tamil Nadu	101
2	Bala	Coimbatore	Tamil Nadu	102
3	Kavitha	Karaikal	Puducherry	103
4	Raj	Madurai	Tamil Nadu	101
5	Kumar	Trichy	Tamil Nadu	102

To find departments with more than 1 employee:

SELECT DepartmentID, COUNT(EmployeeID) AS NumberOfEmployees
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(EmployeeID) > 1;

DepartmentID	NumberOfEmployees
101	2
102	2

HAVING with SUM

Suppose you have a table named Sales that stores sales data.

Table: Sales

SaleID	Salesperson	City	State	Amount
1	Ram	Chennai	Tamil Nadu	5000
2	Karthik	Coimbatore	Tamil Nadu	6000
3	David	Bangalore	Karnataka	5500
4	Kannan	Chennai	Tamil Nadu	7000
5	Siva	Madurai	Tamil Nadu	4500

To find cities with total sales greater than ₹10,000:

SELECT City, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY City
HAVING SUM(Amount) > 10000;

City	TotalSales
Chennai	12000

HAVING with AVG

Suppose you have a table named Orders that stores order details.

Table: Orders

OrderID	CustomerName	City	State	Amount
101	Ram	Chennai	Tamil Nadu	5000
102	Karthik	Coimbatore	Tamil Nadu	6000
103	David	Bangalore	Karnataka	5500
104	Kannan	Chennai	Tamil Nadu	7000
105	Siva	Madurai	Tamil Nadu	4500

To find states with an average order amount greater than ₹5,000:

SELECT State, AVG(Amount) AS AverageOrderAmount
FROM Orders
GROUP BY State
HAVING AVG(Amount) > 5000;

State	AverageOrderAmount
Tamil Nadu	5625

Practical Use Case

Suppose you have a table named Students that stores student details.

Table: Students

StudentID	Name	City	State	Percentage
1	Ram	Chennai	Tamil Nadu	92
2	Karthik	Coimbatore	Tamil Nadu	88
3	David	Bangalore	Karnataka	95
4	Kannan	Karaikal	Puducherry	91
5	Siva	Madurai	Tamil Nadu	89

To find states with an average percentage greater than 90:

SELECT State, AVG(Percentage) AS AveragePercentage
FROM Students
GROUP BY State
HAVING AVG(Percentage) > 90;

State	AveragePercentage
Karnataka	95
Puducherry	91

Key Takeaways

1. The HAVING clause is used to filter groups of rows based on specified conditions.
2. It is typically used with the GROUP BY clause to filter groups after aggregation.
3. Conditions in the HAVING clause often involve aggregate functions like COUNT, SUM, AVG, MIN, and MAX.
4. HAVING is executed after the GROUP BY clause and before the ORDER BY clause.
5. Use HAVING to filter groups and generate meaningful summaries and reports.