- An aggregate may not appear in the WHERE clause.
- The having clause is an additional filter to the where clause.
- An aggregate may appear in the HAVING clause.
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
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.”
Select Emp,Sum(Bonus) From #Rpt Group by Emp having Sum(Bonus) > 12000
Above query will give result