HAVING Clause in SQL
HAVING Clause in SQL
In SQL, the HAVING clause is used to place conditions on groups created by the GROUP BY clause in a SELECT statement. Unlike the WHERE clause, which filters records before grouping, the HAVING clause filters records after grouping.
Complete Python Course with Advance topics:-Click Here
SQL Tutorial :-Click Here
Why Do We Need the HAVING Clause?
The HAVING clause is essential because WHERE cannot be used with aggregate functions like SUM, COUNT, AVG, MIN, and MAX. Both HAVING and WHERE are used for filtering, but they serve different purposes in SQL queries.
Difference Between HAVING and WHERE Clause
One of the most frequently asked interview questions in SQL is the difference between HAVING and WHERE clauses. The main difference is that WHERE filters data before grouping, whereas HAVING filters data after grouping.
Comparison Table:
| HAVING | WHERE |
|---|---|
| The HAVING clause filters data after the GROUP BY clause has been applied. | The WHERE clause filters data before grouping. |
| Used with GROUP BY. | Can be used without GROUP BY. |
| Supports aggregate functions (SUM, COUNT, AVG, etc.). | Does not support aggregate functions. |
| Only works with SELECT statements. | Can be used with SELECT, UPDATE, and DELETE statements. |
| Implemented after GROUP BY. | Implemented before GROUP BY. |
| Works on grouped data (column operations). | Works on individual records (row operations). |
| It is a post-filter. | It is a pre-filter. |
| Used to filter groups of data. | Used to filter individual records. |
Syntax of the HAVING Clause in SQL
SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
GROUP BY column1
HAVING condition;
Examples of the HAVING Clause in SQL
Let’s explore four different examples that demonstrate the use of the HAVING clause with various aggregate functions.
Example 1: Using HAVING with SUM Function
Consider the following Sales table:
| Sales_ID | Salesman | Amount | City |
|---|---|---|---|
| 101 | Rohan | 3000 | Mumbai |
| 102 | Aman | 5000 | Delhi |
| 103 | Suresh | 7000 | Pune |
| 104 | Rahul | 2000 | Mumbai |
| 105 | Mohit | 6000 | Delhi |
If you want to calculate the total sales amount for each city, use:
SELECT SUM(Amount), City FROM Sales GROUP BY City;
Output:
| SUM(Amount) | City |
|---|---|
| 5000 | Mumbai |
| 11000 | Delhi |
| 7000 | Pune |
Now, suppose you want to display only those cities where the total sales exceed 6000. Use the HAVING clause:
SELECT SUM(Amount), City FROM Sales GROUP BY City HAVING SUM(Amount) > 6000;
Output:
| SUM(Amount) | City |
|---|---|
| 11000 | Delhi |
| 7000 | Pune |
Example 2: Using HAVING with COUNT Function
Consider the Students table:
| Roll_No | Name | Marks | Age |
|---|---|---|---|
| 1 | Aryan | 90 | 21 |
| 2 | Soham | 75 | 19 |
| 3 | Neha | 88 | 22 |
| 4 | Raj | 80 | 21 |
| 5 | Simran | 65 | 20 |
| 6 | Rahul | 78 | 22 |
| 7 | Sneha | 92 | 19 |
| 8 | Anuj | 85 | 21 |
To count the number of students by age, use:
SELECT COUNT(Roll_No), Age FROM Students GROUP BY Age;
Output:
| COUNT(Roll_No) | Age |
|---|---|
| 3 | 21 |
| 2 | 19 |
| 2 | 22 |
| 1 | 20 |
Now, to display only ages where at least two students exist:
SELECT COUNT(Roll_No), Age FROM Students GROUP BY Age HAVING COUNT(Roll_No) >= 2;
Output:
| COUNT(Roll_No) | Age |
|---|---|
| 3 | 21 |
| 2 | 19 |
| 2 | 22 |
Example 3: Using HAVING with MIN and MAX Functions
Consider the Employees table:
| Emp_ID | Name | Salary | Department |
|---|---|---|---|
| 201 | Aman | 8500 | HR |
| 202 | Riya | 4500 | IT |
| 203 | Karan | 5000 | IT |
| 204 | Simran | 9200 | Finance |
| 205 | Mohan | 11000 | Marketing |
To find the minimum salary in each department:
SELECT MIN(Salary), Department FROM Employees GROUP BY Department;
Output:
| MIN(Salary) | Department |
|---|---|
| 8500 | HR |
| 4500 | IT |
| 9200 | Finance |
| 11000 | Marketing |
Now, to display departments where the minimum salary is greater than 5000:
SELECT MIN(Salary), Department FROM Employees GROUP BY Department HAVING MIN(Salary) > 5000;
Output:
| MIN(Salary) | Department |
|---|---|
| 8500 | HR |
| 9200 | Finance |
| 11000 | Marketing |
Example 4: Using HAVING with AVG Function
To find the average salary of employees in each department:
SELECT AVG(Salary), Department FROM Employees GROUP BY Department;
Output:
| AVG(Salary) | Department |
|---|---|
| 8500 | HR |
| 4750 | IT |
| 9200 | Finance |
| 11000 | Marketing |
To filter only departments where average salary is more than 7000:
SELECT AVG(Salary), Department FROM Employees GROUP BY Department HAVING AVG(Salary) > 7000;
Output:
| AVG(Salary) | Department |
|---|---|
| 8500 | HR |
| 9200 | Finance |
| 11000 | Marketing |
Download New Real Time Projects :-Click here
Complete Advance AI topics:- CLICK HERE
Conclusion
The HAVING clause is crucial for filtering grouped data in SQL. Unlike WHERE, it allows the use of aggregate functions to refine query results after grouping. Mastering HAVING will significantly improve your SQL skills and data manipulation capabilities.
having clause in sql example
group by clause in sql
group by and having clause in sql
having clause in sql without group by
having clause in sql w3schools
having clause in sql oracle
where clause in sql
having clause example
Post Comment