HAVING Clause in SQL

HAVING Clause in SQL

In SQL, the HAVING clause is used to apply conditions to groups created using the GROUP BY clause in a SELECT statement. While the WHERE clause filters records before grouping, the HAVING clause filters grouped data after the grouping operation.

Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here

Why Do We Need the HAVING Clause?

The HAVING clause is important because the WHERE clause cannot be used with aggregate functions such as SUM, COUNT, AVG, MIN, and MAX. Although both HAVING and WHERE are used for filtering, they are applied at different stages of an SQL query.

Difference Between HAVING and WHERE Clause

One of the most common SQL interview questions is the difference between the HAVING and WHERE clauses. The key difference is that WHERE filters individual records before grouping, while HAVING filters groups after the GROUP BY clause has been applied.

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 such as SUM, COUNT, and AVG.	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.	Works on individual records.
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

Conclusion

The HAVING clause is useful for filtering grouped data in SQL. Unlike WHERE, it allows aggregate functions to be used when refining query results after grouping. Understanding the HAVING clause can help improve SQL skills and make it easier to work with grouped data.

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