In this article, I will try to highlight all the major points related to the Having clause in SQL Server. Use Having instead of Where when you want to establish a condition that involves a grouping (aggregating) function. The Having clause is used with aggregate functions (for example sum, count etc.) because the Where clause is not used with aggregate functions. The Having clause is used in a GROUP BY clause. When GROUP BY is not used, most of the time you will get the same result with the Where or Having clause. So let's have a look at a practical example of how to use the Having Clause in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.
Creating a table in SQL Server
Now we create a table named employee using:
Create table Employee
(
EmpID int,
EmpName varchar(30),
EmpSalary int
)
The following is the sample data for the employee Table:
![Employee-table-in-Sql-Server.jpg]()
The following are some important points related to the Having clause.
1. The Having clause is used only with the Select statement
The Having clause can be used only with a Select statement. If you use the Having clause with an update or delete query in SQL, it will not work. The following example defines the Having clause with an update statement.
Example
Update Statement with Having clause:
Update Employee set [EmpSalary] ='16000' having EmpID=1
Output
![Having-clause-with-update-statement-in-Sql-Server.jpg]()
2. The Having clause is used in a GROUP BY clause with the Select statement
The Having clause in a Select Statement without a GROUP BY clause:
select * from employee HAVING EmpID=1
Output
![Having-clause-without-group-by-statement-in-Sql-Server.jpg]()
The Having clause in a Select Statement with a GROUP BY clause:
select EmpName from Employee
GROUP BY EmpName
HAVING SUM(EmpSalary) <30000
Output
![Having-clause-with-group-by-statement-in-Sql-Server.jpg]()
3. Having clause specifies a search condition for a group or an aggregate function
The following example defines the Having clause with a search condition for a group.
select EmpName, SUM(EmpSalary) as EmpSalary from Employee
GROUP BY EmpName
HAVING SUM(EmpSalary) < 30000
Output
![Having-clause-with-group-statement-in-Sql-Server.jpg]()
4. Having works with Where clause
The following SQL query shows a Having with a Where clause:
select EmpName, SUM(EmpSalary) as EmpSalary from Employee
WHERE EmpName='Smith' OR EmpName='Rahul' or EmpName ='Meths'
GROUP BY EmpName
HAVING SUM(EmpSalary) < 30000
Output
![Having-clause-with-where-in-Sql-Server.jpg]()
5. Having clause with the Order by clause
The following SQL query shows a Having with an Order By clause:
select EmpName, SUM(EmpSalary) as EmpSalary from Employee
GROUP BY EmpName
HAVING SUM(EmpSalary) < 30000
order by EmpName desc
Output
![Having-clause-with-Orderby-in-Sql-Server.jpg]()
6. Having clause with Order by with more than one column
The following SQL query shows a Having with an Order By with more than one column:
select EmpID, EmpName, SUM(EmpSalary) as EmpSalary from Employee
GROUP BY EmpName,EmpID
HAVING SUM(EmpSalary) < 30000
order by EmpName desc
Output
![Having-clause-with-group-bywithmorethanone-column-in-Sql-Server.jpg]()