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
|
||||||||||||||||||||||||||||||
SQL
will generate the below result
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