SQL HAVING Clause

Hello friends, I am writing this after long time. Hope you are doing well. Lastly we have written on Extension Method in C#, Reflection Introduction c#, Insert data into Database table using SQLBulkCopy class in C#, as keyword C#, Import/Upload Excel file in asp.net with C#, The Microsoft Jet database engine cannot open the file. It is already opened exclusively by another user, Call method after specific time interval C#, Alert from code behind asp.net,required field validator in asp.net,Difference between RegisterClientScriptBlock and RegisterStartupScript asp.net, Difference between ref and out parameters. Today I am going to write on SQL HAVING Clause.

Having Clause is used because we can’t use WHERE Clause if we have to use aggregate function in condition. We can use HAVING Clause if we have to use aggregate function in condition.

Syntax of SQL HAVING Clause:

SELECT column_name,column_name1
FROM table_name
WHERE condition
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

I will explain it with example. Let’s assume you have one table student_marks and you have to select student who’s average marks greater than 35. Then you can write SQL HAVING Clause in the query. Following is table data:

CREATE TABLE #Student_marks(id INT, student_name VARCHAR(150), Sub_name VARCHAR(150), Marks decimal)

INSERT INTO #Student_marks
VALUES (1,'Bharat','Physics',40),
       (2,'Bharat','Maths',20),
	   (3,'Bharat','Science',20),
	   (4,'Bhavesh','Physics',50),
       (5,'Bhavesh','Maths',40),
	   (6,'Bhavesh','Science',50),
	   (7,'Mahesh','Physics',10),
       (8,'Mahesh','Maths',20),
	   (9,'Mahesh','Science',20),
	   (10,'Suresh','Physics',60),
       (11,'Suresh','Maths',80),
	   (12,'Suresh','Science',70)

And I want output as shown in following screenshot:

SQL HAVING Clause

You can write query like Following:

SELECT student_name AS Student ,SUM(Marks) AS Total_Marks
FROM #Student_marks
GROUP BY student_name
HAVING AVG(marks) > 35

Try more new interesting queries and let us know you understood it or not. Try Here.
Thanks for reading 🙂

Leave a Reply