Reader Level:
ARTICLE

Using Having Clause in SQL Server 2012

Posted by Rohatash Kumar Articles | SQL Server November 19, 2012
In this article, I will try to highlight all the major points related to the Having clause in SQL Server.
  • 0
  • 0
  • 7850

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

COMMENT USING

Trending up