Saturday, September 07, 2013

TSQL Interview: Having Clause

Having clause work like a where clause with group clause. It is filter conditioning for group by data.
Where clause work for filter condition on Normal Data while having clause works for group data such as count() > value or sum() > value.
Create Table #Employee(Id int identity(1,1),Name Varchar(100),Designation Varchar(100),Salary int)

Insert into #Employee Values('Rahul','SSE',30000)
Insert into #Employee Values('Rohit','SE',20000)
Insert into #Employee Values('Ronit','SE',20000)
Insert into #Employee Values('Rupesh','TL',40000)
Insert into #Employee Values('Ahishek','AM',50000)
Insert into #Employee Values('Rakesh','SSE',30000)
Insert into #Employee Values ('Ashok','AM',50000)
Insert into #Employee Values ('Puneet','SE',20000)
Insert into #Employee Values ('Vineet','SSE',40000)

/* Having Clause data*/

Select count(*),Designation from #Employee 
group by Designation
having count(*) > 2

Will return below two rows as the designation which are associated more than two people
3 SE
3 SSE
Where clause can also be used with having in that case first where filter at main data and then group operation will performed and then having filter the group data.

Keyword: Difference between having and where clause.Having Clause

No comments:

Post a Comment