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. 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:

 

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

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

The Having clause in a Select Statement with a GROUP BY clause:

select EmpName from Employee

GROUP BY EmpName

HAVING SUM(EmpSalary) <30000

 

Output

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

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

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

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