In this tutorial we will learn how to use HAVING clause with an aggregate function in SQL Server with Example.
Having clause enables you to filter data based on the group functions. This is similar to WHERE condition. But it must use with group functions. Group functions cannot be used in WHERE Clause. But Group functions can be used in HAVING clause. The HAVING clause must use with an aggregate functions but WHERE clause could not be used with aggregate functions. COUNT, MAX, SUM, AVG, etc.
Following example Without GROUP BY HAVING clause
F.ammount FROM STUDENT AS S INNER JOIN Fee AS F ON S.id=F.StudentId
Above example showing Total records of student table.
If you want to select the students that has total amount paid more than 4000, the SQL query would be like
SUM(F.ammount) AS amount FROM STUDENT AS S INNER JOIN Fee AS F ON S.id=F.StudentId
group by S.id,S.Name,S.Mobile,S.address1 having sum(f.ammount)>4000
Above example show grouping records with having condition. Which student has total amount paid more than 4000.