Using Having Clause in SQL Server 2012

In this article, I will try to highlight all the major points related to the Having clause in SQL Server.

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:
  1. Create table Employee  
  2. (  
  3.     EmpID int,  
  4.     EmpName varchar(30),  
  5.     EmpSalary int  
  6. )  
The following is the sample data for the employee Table:
 
Employee-table-in-Sql-Server.jpg 
 

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:
  1. Update Employee set [EmpSalary] ='16000'  having EmpID=1  
Output
 
Having Clause in SQL Server 
 
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:
  1. select * from employee HAVING EmpID=1  
Output
 
Having Clause in SQL Server 
 
The Having clause in a Select Statement with a GROUP BY clause:
  1. select EmpName from Employee  
  2. GROUP BY EmpName  
  3. HAVING SUM(EmpSalary) <30000  
Output
 
Having Clause in SQL Server 
 
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.
  1. select  EmpName, SUM(EmpSalary) as EmpSalary from Employee  
  2. GROUP BY EmpName  
  3. HAVING SUM(EmpSalary) < 30000  
Output
 
Having Clause in SQL Server 
 
4. Having works with Where clause
 
The following SQL query shows a Having with a Where clause:
  1. select EmpName, SUM(EmpSalary) as EmpSalary from Employee  
  2. WHERE EmpName='Smith' OR EmpName='Rahul' or EmpName ='Meths'  
  3. GROUP BY EmpName  
  4. HAVING SUM(EmpSalary) < 30000  
Output
 
Having Clause in SQL Server 
 
5. Having clause with the Order by clause
 
The following SQL query shows a Having with an Order By clause:
  1. select EmpName, SUM(EmpSalary) as EmpSalary from Employee  
  2. GROUP BY EmpName  
  3. HAVING SUM(EmpSalary) < 30000  
  4. order by EmpName desc  
Output
 
Having Clause in SQL Server 
 
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:
  1. select EmpID, EmpName, SUM(EmpSalary) as EmpSalary from Employee  
  2. GROUP BY EmpName,EmpID  
  3. HAVING SUM(EmpSalary) < 30000  
  4. order by EmpName desc  
Output
 
Having Clause in SQL Server