Introduction:

In this tutorial we will learn how to use HAVING clause with an aggregate function  in SQL Server with Example.

Description:

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.

For Example:

Following example Without GROUP BY HAVING clause

SELECT S.id,S.Name,S.Mobile,S.address1,
F.ammount FROM STUDENT AS S INNER JOIN Fee AS F ON S.id=F.StudentId

Result:

 Having clause in SQL Server

Above example showing Total records of student table.

Having clause

If you want to select the students that has total amount paid more than 4000, the SQL query would be like 

SELECT S.id,S.Name,S.Mobile,S.address1,
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

Result:

 Having clause in SQL Server

Above example show grouping records with having condition. Which student has total amount paid more than 4000.

Thanks.

 


Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

You may also like