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

EmployeeIDNameCityStateDepartmentID
1AnandChennaiTamil Nadu101
2BalaCoimbatoreTamil Nadu102
3KavithaKaraikalPuducherry103
4RajMaduraiTamil Nadu101
5KumarTrichyTamil Nadu102

To find departments with more than 1 employee:

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

​
HAVING with SUM

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

Table: Sales

SaleIDSalespersonCityStateAmount
1RamChennaiTamil Nadu5000
2KarthikCoimbatoreTamil Nadu6000
3DavidBangaloreKarnataka5500
4KannanChennaiTamil Nadu7000
5SivaMaduraiTamil Nadu4500

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;
CityTotalSales
Chennai12000

​
HAVING with AVG

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

Table: Orders

OrderIDCustomerNameCityStateAmount
101RamChennaiTamil Nadu5000
102KarthikCoimbatoreTamil Nadu6000
103DavidBangaloreKarnataka5500
104KannanChennaiTamil Nadu7000
105SivaMaduraiTamil Nadu4500

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;
StateAverageOrderAmount
Tamil Nadu5625

​
Practical Use Case

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

Table: Students

StudentIDNameCityStatePercentage
1RamChennaiTamil Nadu92
2KarthikCoimbatoreTamil Nadu88
3DavidBangaloreKarnataka95
4KannanKaraikalPuducherry91
5SivaMaduraiTamil Nadu89

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;
StateAveragePercentage
Karnataka95
Puducherry91

​
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.