SQL Topics
HAVING Clause
title: HAVING Clause
When working with grouped data, filtering individual rows using the WHERE clause is often not enough. Businesses frequently need to filter groups after aggregate calculations have been performed.
For example:
- A company wants departments with more than 20 employees.
- A school wants classes whose average marks exceed 80.
- An online store wants product categories generating sales above ₹50,000.
- A bank wants branches with more than 1,000 customers.
The WHERE clause cannot directly filter aggregate results because it works before grouping occurs.
To solve this problem, SQL provides the HAVING Clause.
The HAVING clause is used to filter groups created by the GROUP BY clause. It works after grouping and aggregation have been completed, allowing you to apply conditions to summarized data.
This makes HAVING an essential tool for reporting, business intelligence, and data analysis.
What is HAVING?
The HAVING clause filters grouped records after aggregate functions have been calculated.
Example:
SELECT Department,
COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;Result:
| Department | TotalEmployees |
|---|---|
| IT | 10 |
| HR | 8 |
Departments with fewer than 5 employees are excluded.
Why is HAVING Important?
Consider an Employees table:
| EmployeeID | Department |
|---|---|
| 1 | IT |
| 2 | IT |
| 3 | HR |
| 4 | HR |
| 5 | HR |
Suppose management wants departments containing more than two employees.
Without HAVING:
All Departments ReturnedWith HAVING:
SELECT Department,
COUNT(*)
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 2;Result:
| Department | COUNT(*) |
|---|---|
| HR | 3 |
Only qualifying groups appear.
Basic HAVING Syntax
The general syntax is:
SELECT ColumnName,
AggregateFunction(ColumnName)
FROM TableName
GROUP BY ColumnName
HAVING Condition;Example:
SELECT Department,
COUNT(*)
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;The HAVING clause filters grouped results.
Understanding the Syntax
Example:
SELECT Department,
COUNT(*)
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;SELECT
Specifies columns to display.
COUNT(*)
Calculates employee counts.
GROUP BY
Groups rows by department.
HAVING
Filters groups.
COUNT(*) > 5
Condition applied after grouping.
Execution Order
Understanding execution order is important.
SQL processes the query as follows:
FROM
↓
WHERE
↓
GROUP BY
↓
HAVING
↓
SELECT
↓
ORDER BYNotice:
WHERE → Before Grouping
HAVING → After GroupingThis is the key difference.
HAVING with COUNT()
COUNT() is commonly used with HAVING.
Example:
SELECT Department,
COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) >= 10;Result:
Only departments containing at least 10 employees are returned.
HAVING with SUM()
Example:
SELECT Department,
SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department
HAVING SUM(Salary) > 500000;Result:
Departments with salary expenses greater than ₹500,000 are displayed.
HAVING with AVG()
Example:
SELECT Department,
AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 40000;Result:
Departments whose average salary exceeds ₹40,000 are returned.
HAVING with MAX()
Example:
SELECT Department,
MAX(Salary) AS HighestSalary
FROM Employees
GROUP BY Department
HAVING MAX(Salary) > 100000;Result:
Departments containing employees earning above ₹100,000 are shown.
HAVING with MIN()
Example:
SELECT Department,
MIN(Salary) AS LowestSalary
FROM Employees
GROUP BY Department
HAVING MIN(Salary) > 20000;Result:
Only departments whose lowest salary exceeds ₹20,000 appear.
Sample Table
Example:
CREATE TABLE Employees (
EmployeeID INT,
Name VARCHAR(100),
Department VARCHAR(50),
Salary DECIMAL(10,2)
);Data:
INSERT INTO Employees VALUES
(1, 'Rahul', 'IT', 50000),
(2, 'Priya', 'IT', 60000),
(3, 'Amit', 'HR', 30000),
(4, 'Neha', 'HR', 40000),
(5, 'Rohan', 'Finance', 80000);Finding Departments with Multiple Employees
Query:
SELECT Department,
COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 1;Result:
| Department | TotalEmployees |
|---|---|
| IT | 2 |
| HR | 2 |
Finance is excluded because it contains only one employee.
HAVING with Multiple Conditions
Example:
SELECT Department,
AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 40000
AND COUNT(*) > 1;Result:
Only departments satisfying both conditions are returned.
HAVING with ORDER BY
Grouping, filtering, and sorting are often combined.
Example:
SELECT Department,
SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department
HAVING SUM(Salary) > 50000
ORDER BY TotalSalary DESC;Result:
Departments are sorted by total salary.
HAVING vs WHERE
This is one of the most important SQL interview topics.
| Feature | WHERE | HAVING |
|---|---|---|
| Filters Rows | Yes | No |
| Filters Groups | No | Yes |
| Used Before GROUP BY | Yes | No |
| Used After GROUP BY | No | Yes |
| Works with Aggregate Functions | No | Yes |
WHERE Example
SELECT *
FROM Employees
WHERE Salary > 50000;Filters individual rows.
HAVING Example
SELECT Department,
AVG(Salary)
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 50000;Filters groups.
Combining WHERE and HAVING
Both clauses can be used together.
Example:
SELECT Department,
COUNT(*) AS TotalEmployees
FROM Employees
WHERE Salary > 30000
GROUP BY Department
HAVING COUNT(*) > 1;Process:
WHERE → Filter Rows
↓
GROUP BY → Create Groups
↓
HAVING → Filter GroupsThis is extremely common in reporting queries.
Real-World Example
Consider an e-commerce system.
Orders table:
| Category | Amount |
|---|---|
| Electronics | 20000 |
| Electronics | 30000 |
| Clothing | 5000 |
| Clothing | 3000 |
Query:
SELECT Category,
SUM(Amount) AS TotalSales
FROM Orders
GROUP BY Category
HAVING SUM(Amount) > 10000;Result:
| Category | TotalSales |
|---|---|
| Electronics | 50000 |
Only high-performing categories appear.
Common Errors
Using Aggregate Functions in WHERE
Wrong:
SELECT Department
FROM Employees
WHERE COUNT(*) > 2;Error occurs.
Correct:
HAVING COUNT(*) > 2;Missing GROUP BY
Wrong:
SELECT Department,
COUNT(*)
FROM Employees
HAVING COUNT(*) > 2;Usually requires GROUP BY.
Confusing WHERE and HAVING
Many beginners use HAVING when WHERE is more appropriate.
Use:
WHEREfor row filtering.
Use:
HAVINGfor group filtering.
Best Practices
Use WHERE Before HAVING
Filter rows first whenever possible.
Example:
WHERE Salary > 20000This reduces the amount of data that must be grouped.
Use Meaningful Aliases
Example:
COUNT(*) AS TotalEmployeesImproves readability.
Combine with ORDER BY
Sorting grouped results often improves analysis.
Avoid Unnecessary HAVING Clauses
Use HAVING only when filtering grouped data.
Write Readable Queries
Format aggregate queries clearly.
Common Interview Questions
What is the purpose of HAVING?
HAVING filters grouped records after aggregation.
What is the difference between WHERE and HAVING?
WHERE filters rows before grouping, while HAVING filters groups after grouping.
Can HAVING use aggregate functions?
Yes.
Examples:
COUNT()
SUM()
AVG()
MAX()
MIN()Can WHERE and HAVING be used together?
Yes.
WHERE filters rows and HAVING filters groups.
Summary
The HAVING clause is a powerful SQL feature used to filter grouped data after aggregate calculations have been performed. It plays a critical role in reporting, analytics, and business intelligence applications.
In this lesson, you learned:
- What HAVING is
- Why it is important
- HAVING syntax
- HAVING with COUNT()
- HAVING with SUM()
- HAVING with AVG()
- HAVING vs WHERE
- Combining WHERE and HAVING
- Common mistakes
- Best practices
Understanding HAVING is essential because most advanced SQL reports require filtering aggregated results efficiently.
Next Step
Continue to the next lesson:
DISTINCT Keyword →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for HAVING Clause.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, filtering, having
Related SQL Topics