What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?


WHERE CLAUSE:-

  • An aggregate may not appear in the WHERE clause.
  • The having clause is an additional filter to the where clause.

HAVING CLAUSE:-

  • An aggregate may appear in the HAVING clause.

Example:-

Create table #Rpt
 (
  Emp varchar(100),
  Bonus int
 )
 
Insert into #Rpt
Select 'Hitesh',5000 union
Select 'Nitesh',8000 union
Select 'Ritvij',10000 union
Select 'Anuraag',15000 union
Select 'Hitesh',6000 union
Select 'Nitesh',7000 union
Select 'Ritvij',8000 union
Select 'Anuraag',9000 


Emp Bonus
Anuraag 9000
Anuraag 15000
Hitesh 5000
Hitesh 6000
Nitesh 7000
Nitesh 8000
Ritvij 8000
Ritvij 10000

Where Clause

Select Emp,Sum(Bonus)
From #Rpt 
Where Sum(Bonus) > 8000 group by Emp


Above query will give an error “An aggregate may not appear in the WHERE clause unless it is in a sub query contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.”

Having Clause

Select Emp,Sum(Bonus)
From #Rpt 
Group by Emp having Sum(Bonus) > 12000


Above query will give result
Emp Bonus
Anuraag 24000
Nitesh 15000
Ritvij 18000