Friday, January 31, 2014

Difference between the having clause and the where clause?


Difference between the having clause and the where clause?
 
The basic difference between the Where and having clause is that where clause cant filter aggregate condition while the having clause work on filtering the aggregate condition.
i.e. where filters before aggregated data while having after aggregated data.
 
Let’s understand it with an example. We have a Table with employee Name and SALARY
EmployeeData
Employee
Des
A
SE
B
SSE
C
SSE
D
TL
E
SE
F
TL
H
AM
 
 
 
Now we want the total no of employee in a desgination.
 
Select Des, count(Employee) from EmployeeData group by Des;
SQL will generate the below result
Employee
Sum(Bonus)
SSE
2
SE
2
TL
2
AM
1
 
Now I want the Designation to which more than one employee associated.
Thinks we need to understand that where clause doesn’t work with aggregate condition i.e you cant say that where count(employee) > 2 and also you  can’t place where after group by.
Now  for this we use having clause it’s a filter condition for aggregate in group by. Your query will be like that
Select Des, count(Employee) from Employeedata group by des having count(Employee) > 1
It will return
Employee
No of employee
SSE
2
SE
2
TL
2

 

Now how to use where and having in same query

We want to retrieve list where employee count is  > 1 but we don’t want TL should come in the list.

Select Des, Count(Employee) from EmployeeData where Des <> ‘TL’  group by Des  Having Count(employee) > 1.

Above query shows how where filter before group by and having filter after group by at aggregated level.

No comments:

Post a Comment