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
- )
Having Clause in SQL Server
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
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
The Having clause in a Select Statement with a GROUP BY clause:
- select EmpName from Employee
- GROUP BY EmpName
- HAVING SUM(EmpSalary) <30000
Join the conversation! Your thoughts help the community grow.