The HAVING clause in SQL is used when we need to filter the result of a group or an aggregate calculation. It is especially useful when a query first combines records into groups using GROUP BY and then needs to keep only those groups that satisfy a particular condition.
For example, suppose a college database contains thousands of student records. An administrator may want to display only those courses in which more than 40 students are enrolled. Similarly, a business may want to find product categories whose total sales are greater than ₹1,00,000. These requirements involve calculations over multiple rows, so the condition needs to be applied after the grouping or aggregation has been performed.
The HAVING clause provides a convenient way to perform this type of filtering. It is commonly used with aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX().
The HAVING clause specifies a condition for groups produced by a grouped query. Instead of examining one row at a time, it evaluates information calculated for a group of rows.
A simple way to remember the difference is:
Therefore, HAVING is particularly useful when the condition depends on an aggregate value.
Consider a table containing employee information. If we want employees whose salary is greater than ₹50,000, a normal WHERE condition is sufficient because salary belongs to an individual row.
However, suppose the requirement is to find departments whose average salary is greater than ₹50,000. The average salary does not belong to one particular row. It is calculated from several employees. This is where HAVING becomes useful.
The HAVING clause is therefore commonly used for:
SELECT column_name, aggregate_function(column_name) FROM table_name GROUP BY column_name HAVING condition;
The GROUP BY clause creates the groups, while the HAVING clause evaluates each group against the specified condition.
SELECT Department, COUNT(*) AS EmployeeCount FROM Employee GROUP BY Department HAVING COUNT(*) > 5;
This query returns only those departments that contain more than five employees.
For the examples in this tutorial, consider the following Student table:
| StudentID | StudentName | Course | Marks |
|---|---|---|---|
| 101 | Rahul | BCA | 80 |
| 102 | Priya | BCA | 90 |
| 103 | Amit | MCA | 75 |
| 104 | Neha | MCA | 85 |
| 105 | Rohan | B.Tech | 88 |
| 106 | Anjali | B.Tech | 92 |
The COUNT() function determines how many rows belong to each group. HAVING can then be used to keep groups whose count satisfies a particular condition.
SELECT Course, COUNT(*) AS TotalStudents FROM Student GROUP BY Course HAVING COUNT(*) > 1;
| Course | TotalStudents |
|---|---|
| BCA | 2 |
| MCA | 2 |
| B.Tech | 2 |
The query first creates one group for each course. COUNT() calculates the number of students in every group, and HAVING removes groups whose count is not greater than one.
SUM() is useful when a grouped query needs to calculate a total. HAVING can then remove groups whose calculated total does not meet the required limit.
| Category | SalesAmount |
|---|---|
| Electronics | 5000 |
| Electronics | 7000 |
| Books | 3000 |
| Books | 2500 |
SELECT Category, SUM(SalesAmount) AS TotalSales FROM Sales GROUP BY Category HAVING SUM(SalesAmount) > 5000;
Electronics has a combined sales value of 12,000, so it satisfies the condition. Books has a total of 5,500 and also satisfies the condition in this example.
AVG() calculates the average value for each group. This is particularly useful for academic reports, salary analysis, customer ratings, and performance reports.
SELECT Course, AVG(Marks) AS AverageMarks FROM Student GROUP BY Course HAVING AVG(Marks) > 80;
The query returns only courses whose average marks are greater than 80.
| Course | AverageMarks |
|---|---|
| BCA | 85 |
| B.Tech | 90 |
The MIN() function finds the smallest value within each group. HAVING can be used to select groups according to that minimum value.
SELECT Course, MIN(Marks) AS LowestMarks FROM Student GROUP BY Course HAVING MIN(Marks) > 70;
This query returns courses where the lowest mark in the group is greater than 70.
Notice that the condition is not checking one particular student's marks. It is checking the minimum value calculated for the complete course group.
MAX() returns the largest value found within each group. It can be combined with HAVING when we want to filter groups according to their highest value.
SELECT Course, MAX(Marks) AS HighestMarks FROM Student GROUP BY Course HAVING MAX(Marks) > 85;
Only courses whose highest recorded mark is greater than 85 will be returned.
WHERE and HAVING both perform filtering, but they are used at different stages of a grouped query. Understanding this distinction is one of the most important concepts for SQL beginners.
| WHERE | HAVING |
|---|---|
| Filters individual rows. | Filters groups. |
| Normally applied before grouping. | Applied after grouping in the logical query-processing sequence. |
| Commonly used for row-level conditions. | Commonly used for aggregate conditions. |
| Can reduce the number of rows before GROUP BY. | Reduces the groups produced by the grouped query. |
| Example: Marks > 60. | Example: COUNT(*) > 10. |
Both clauses can be used in the same query when the requirement contains a row-level condition as well as a group-level condition.
SELECT Course, COUNT(*) AS TotalStudents, AVG(Marks) AS AverageMarks FROM Student WHERE Marks >= 60 GROUP BY Course HAVING COUNT(*) > 1;
Here, WHERE first removes students whose marks are below 60. The remaining records are grouped by course. Finally, HAVING keeps only those courses containing more than one remaining student.
The order in which SQL is written is not exactly the same as the logical order in which the database evaluates the different clauses. A simplified logical sequence for a grouped query is:
This explains why a condition on an aggregate result is normally placed in HAVING rather than WHERE.
A HAVING condition can contain more than one requirement. Logical operators such as AND and OR can be used to build more specific reports.
SELECT Department, COUNT(*) AS EmployeeCount, AVG(Salary) AS AverageSalary FROM Employee GROUP BY Department HAVING COUNT(*) > 10 AND AVG(Salary) > 50000;
The result contains departments that satisfy both conditions: more than ten employees and an average salary above 50,000.
SELECT Course, COUNT(*) AS TotalStudents FROM Student GROUP BY Course HAVING COUNT(*) > 100 OR COUNT(*) < 20;
This query keeps groups whose student count falls into either of the two specified ranges.
GROUP BY is not restricted to a single column. Multiple columns can be used when the report needs a more detailed grouping.
SELECT Course, Semester, COUNT(*) AS StudentCount FROM Student GROUP BY Course, Semester HAVING COUNT(*) > 20;
In this example, a separate group is created for every Course and Semester combination. HAVING then removes combinations containing 20 or fewer students.
ORDER BY can be placed after HAVING when the final grouped results need to be sorted.
SELECT Category, SUM(SalesAmount) AS TotalSales FROM Sales GROUP BY Category HAVING SUM(SalesAmount) > 10000 ORDER BY TotalSales DESC;
The query first calculates category-wise sales, keeps categories whose total exceeds 10,000, and then sorts the remaining categories from highest sales to lowest sales.
Real database applications frequently store information in separate related tables. GROUP BY, JOIN, and HAVING can be combined to produce reports from those tables.
SELECT d.DepartmentName, COUNT(e.EmployeeID) AS EmployeeCount FROM Department d INNER JOIN Employee e ON d.DepartmentID = e.DepartmentID GROUP BY d.DepartmentName HAVING COUNT(e.EmployeeID) > 20;
This query joins department and employee information, counts employees for every department, and displays only departments having more than 20 employees.
A LEFT JOIN is useful when we also want to consider groups that do not have matching records.
SELECT d.DepartmentName, COUNT(e.EmployeeID) AS EmployeeCount FROM Department d LEFT JOIN Employee e ON d.DepartmentID = e.DepartmentID GROUP BY d.DepartmentName HAVING COUNT(e.EmployeeID) = 0;
Because COUNT() is applied to the employee ID column rather than COUNT(*), departments without matching employees can be identified.
A subquery can be used when a grouped value needs to be compared with another calculated value.
SELECT Department,
AVG(Salary) AS DepartmentAverage
FROM Employee
GROUP BY Department
HAVING AVG(Salary) >
(
SELECT AVG(Salary)
FROM Employee
);
The inner query calculates the average salary of all employees. The outer query calculates the average salary department-wise. HAVING then keeps departments whose average is higher than the overall average.
Sometimes counting rows is not enough. We may need to count unique values within each group. COUNT(DISTINCT column_name) can be used for this purpose.
SELECT Department, COUNT(DISTINCT Designation) AS DesignationCount FROM Employee GROUP BY Department HAVING COUNT(DISTINCT Designation) > 3;
This query identifies departments that contain more than three different designations.
Suppose a university wants to identify courses with large enrollments. The Student table contains one row for every enrolled student.
SELECT Course, COUNT(*) AS StudentCount FROM Student GROUP BY Course HAVING COUNT(*) > 50;
The resulting report can help the administration identify courses that may require additional classrooms, laboratory resources, or teaching staff.
A company may want to find departments where the average salary is above a defined threshold.
SELECT Department, AVG(Salary) AS AverageSalary FROM Employee GROUP BY Department HAVING AVG(Salary) > 60000;
The query produces a department-level salary report instead of displaying every employee individually.
An online shopping platform may store multiple orders for every product category. Management may be interested only in categories that generate substantial revenue.
SELECT ProductCategory, SUM(OrderAmount) AS TotalRevenue FROM Orders GROUP BY ProductCategory HAVING SUM(OrderAmount) > 100000;
The output contains only product categories whose combined order value exceeds 100,000.
A banking system can use HAVING to identify branches whose total account balance exceeds a specified amount.
SELECT BranchName, SUM(AccountBalance) AS TotalBalance FROM Accounts GROUP BY BranchName HAVING SUM(AccountBalance) > 1000000;
Such a report can be useful for branch-level financial analysis.
A hospital may store patient records along with the department responsible for treatment. Administrators can use grouped queries to identify departments handling a high number of patients.
SELECT Department, COUNT(*) AS PatientCount FROM Patients GROUP BY Department HAVING COUNT(*) > 200;
The result can provide a quick overview of departments with comparatively high patient volumes.
A common misconception is that HAVING can only appear when GROUP BY is present. In standard SQL, HAVING can also be used with an aggregate query that produces a single group, depending on the database system and query structure.
SELECT COUNT(*) AS TotalStudents FROM Student HAVING COUNT(*) > 100;
Here, the complete result set is treated as one aggregate group. The row is returned only when the total number of students is greater than 100.
For beginners, however, the most common use of HAVING is with GROUP BY.
HAVING is designed primarily for filtering groups and aggregate results. Some database systems allow different forms of HAVING usage, but using HAVING for ordinary row filtering is usually unnecessary.
If the condition concerns individual rows, WHERE is generally the clearer and more appropriate choice.
The performance of a grouped query depends on several factors, including the size of the table, the grouping operation, indexes, filtering conditions, database engine, and query design.
One useful practice is to eliminate unnecessary rows before grouping whenever possible.
SELECT Department, COUNT(*) AS EmployeeCount FROM Employee WHERE Salary > 30000 GROUP BY Department HAVING COUNT(*) > 10;
In this query, WHERE removes employees earning 30,000 or less before the grouping operation. HAVING then evaluates the number of remaining employees in each department.
A frequent beginner mistake is using HAVING for a simple row-level condition. If the condition does not depend on a group calculation, WHERE is usually more suitable.
When a query selects a grouping column together with an aggregate function, the appropriate GROUP BY expression must normally be supplied.
COUNT(), SUM(), AVG(), MIN(), and MAX() answer different questions. Choosing an inappropriate function can produce a logically incorrect report even when the SQL syntax is valid.
Grouping by too many columns can produce very small groups and make the report harder to understand.
If a row-level condition can be applied using WHERE, doing so can reduce the amount of data that reaches the grouping stage.
| GROUP BY | HAVING |
|---|---|
| Creates groups from rows. | Filters the groups. |
| Specifies how records should be categorized. | Specifies which groups should remain. |
| Commonly used with aggregate functions. | Frequently uses aggregate functions in its conditions. |
| Example: GROUP BY Department | Example: HAVING COUNT(*) > 10 |
The following two queries demonstrate the conceptual difference clearly.
SELECT * FROM Employee WHERE Salary > 50000;
This query selects individual employees whose salary is greater than 50,000.
SELECT Department, AVG(Salary) AS AverageSalary FROM Employee GROUP BY Department HAVING AVG(Salary) > 50000;
This query selects departments whose calculated average salary is greater than 50,000.
The first query evaluates individual records. The second evaluates grouped information.
HAVING is a clause used to filter groups produced by a grouped query.
It is used when a condition needs to be applied to grouped or aggregate results.
WHERE normally filters individual rows, whereas HAVING filters groups or aggregate results.
Yes. GROUP BY and HAVING are very commonly used together.
Yes. COUNT() is one of the most common aggregate functions used in HAVING conditions.
Yes. SUM() can be used to filter groups according to their calculated total.
Yes. AVG() can be used to filter groups according to their average value.
Yes. MIN() can be included in a HAVING condition.
Yes. MAX() can be used when groups need to be filtered according to their highest value.
Yes. WHERE can filter rows before grouping, while HAVING can filter the resulting groups.
Yes. JOIN, GROUP BY, and HAVING are frequently combined in reporting queries.
Yes. ORDER BY can sort the groups that remain after the HAVING condition is applied.
It means that only groups containing more than five rows should be included in the result.
It keeps groups whose combined SalesAmount is greater than 10,000.
It keeps groups whose calculated average marks are greater than 70.
Yes. AND and OR can be used to combine multiple group-level conditions.
Yes. A grouped query can contain several grouping columns and HAVING can filter the resulting combinations.
Yes. A subquery can be useful when an aggregate result needs to be compared with another calculated value.
Yes, aggregate queries can use HAVING without an explicit GROUP BY in SQL systems that support this form.
No. Their purpose is different: WHERE normally works with rows, while HAVING works with groups or aggregate results.
WHERE is logically evaluated before GROUP BY and HAVING.
It allows unwanted rows to be removed before grouping and makes the purpose of the query clearer.
Yes. It is commonly used for reports involving totals, averages, counts, and other grouped measurements.
Yes. For example, it can identify courses whose average marks exceed a specified value.
HAVING is an important part of grouped SQL queries and is frequently required for database reporting and analytical problems.
The SQL HAVING clause provides a way to filter grouped information after aggregate calculations have been performed. It becomes especially valuable when a database query needs to answer questions about groups rather than individual records.
For example, WHERE can identify individual employees earning more than a particular salary, while HAVING can identify departments whose average salary exceeds that amount. Similarly, HAVING can be used to find courses with a specific enrollment level, product categories with high revenue, or branches with large account balances.
The most useful combination to remember is WHERE → GROUP BY → HAVING. WHERE can reduce the input rows, GROUP BY organizes the remaining rows into groups, and HAVING filters those groups according to aggregate conditions.
Once you understand HAVING together with COUNT(), SUM(), AVG(), MIN(), MAX(), JOIN, and ORDER BY, you can create much more useful SQL reports and analytical queries.
| Concept | Purpose |
|---|---|
| WHERE | Filters individual rows. |
| GROUP BY | Creates groups from rows. |
| HAVING | Filters groups. |
| COUNT() | Counts rows or values. |
| SUM() | Calculates a total. |
| AVG() | Calculates an average. |
| MIN() | Finds the smallest value. |
| MAX() | Finds the largest value. |
| ORDER BY | Sorts the final result. |