Please enable Javascript to correctly display the contents on Dot Net Tricks!

Definition, Use of Group by and Having Clause

  Author : Shailendra Chauhan
Updated On : 23 Sep 2016
Total Views : 150,314   
Support : SQL Server 2005,2008,2012
 

In Sql Server, we have group by clause for grouping the records of the database table according to our need. We use having clause to filter data that we get from group by clause.Having clause operates only on group by clause means to use having clause we need to use group by clause first. Lets go through both the clauses.

Group By Clause

Group By clause is used for grouping the records of the database table(s).This clause creates a single row for each group and this process is called aggregation. To use group by clause we have to use at least one aggregate function in Select statement. We can use group by clause without where clause.

Syntax for Group By Clause

 SELECT Col1, Col2, Aggreate_function 
FROM Table_Name 
WHERE Condition 
GROUP BY Col1, Col2 

Let's see how the Group By clause works. Suppose we have a table StudentMarks that contains marks in each subject of the student.

 Create table StudentMarks
(
 st_RollNo int ,
 st_Name varchar(50),
 st_Subject varchar(50),
 st_Marks int
)
--Insert data in StudentMarks table
insert into StudentMarks(st_RollNo,st_Name,st_Subject,st_Marks)
values(1,'Mohan','Physics',75);
insert into StudentMarks(st_RollNo,st_Name,st_Subject,st_Marks)
values(1,'Mohan','Chemistry',65);
insert into StudentMarks(st_RollNo,st_Name,st_Subject,st_Marks)
values(1,'Mohan','Math',70);
insert into StudentMarks(st_RollNo,st_Name,st_Subject,st_Marks) values(2,'Vipul','Physics',70);
insert into StudentMarks(st_RollNo,st_Name,st_Subject,st_Marks)
values(2,'Vipul','Chemistry',75);
insert into StudentMarks(st_RollNo,st_Name,st_Subject,st_Marks) values(2,'Vipul','Math',60);
insert into StudentMarks(st_RollNo,st_Name,st_Subject,st_Marks)
values(3,'Jitendra','Physics',85);
insert into StudentMarks(st_RollNo,st_Name,st_Subject,st_Marks)
values(3,'Jitendra','Chemistry',75);
insert into StudentMarks(st_RollNo,st_Name,st_Subject,st_Marks)
values(3,'Jitendra','Math',60);
--Now see data in table
select * from StudentMarks 
 -- Group By clause without where condition
SELECT st_Name, SUM(st_Marks) AS 'Total Marks'
FROM StudentMarks
GROUP BY st_Name; 
 -- Group By clause with where condition
SELECT st_Name, SUM(st_Marks) AS 'Total Marks'
FROM StudentMarks
where st_Name='Mohan'
GROUP BY st_Name; 
 -- Group By clause to find max marks in subject
SELECT st_Subject,max(st_Marks) AS 'Max Marks in Subject'
FROM StudentMarks
GROUP BY st_Subject; 

Having Clause

This clause operates only on group rows of table(s) and act as a filter like as where clause. We use having clause to filter data that we get from group by clause. To use having clause we need to use group by clause first.

 -- Having clause without where condition
SELECT st_Name, SUM(st_Marks) AS 'Students Scored > 205'
FROM StudentMarks
GROUP BY st_Name
HAVING SUM(st_Marks) > 205 
 -- Having clause with where condition
SELECT st_Name, SUM(st_Marks) AS 'Students Scored > 205'
FROM StudentMarks
where st_RollNo between 1 and 3
GROUP BY st_Name
HAVING SUM(st_Marks) > 205 

Note

  1. To use Group By Clause, we need to use at least one aggregate function

  2. All columns that are not used by aggregate function(s) must be in the Group By list

  3. We can use Group By Clause with or without Where Clause.

  4. To use Having Clause, we have to use Group By Clause since it filters data that we get from Group By Clause

Summary

In this article I try to explain Group By and Having Clause. I hope after reading this article you are familiar with Group By and Having Clause. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.


YOU MIGHT LIKE
Free Interview Books
 
COMMENTS (0)
13 FEB
ASP.NET Core Development (online)

07:00 AM-09:00 AM IST( MON, WED, FRI)

Know More
25 JAN
Angular2 and Angular1 Development (online)

09:00 PM-10:30 PM IST(MON-FRI)

Know More
24 JAN
ASP.NET MVC with Angular2 Development (online)

07:00 AM-09:00 AM IST(Tuesday & Thursday)

Know More
20 JAN
NODEJS & MEAN Stack 2 Development (online)

09:00 PM-11:00 PM IST( MON, WED, FRI)

Know More
15 JAN
PPC Marketing (offline)

04:00 PM-05:300 PM

14 JAN
Xamarin Forms : Build Cross-platform Apps (offline)

05:00 PM-06:30 PM IST

10 JAN
ASP.NET MVC with AngularJS Development (online)

09:00 PM-11:00 PM IST on (TUE, THRU,SAT)

3 JAN
ASP.NET MVC with AngularJS Development (online)

07:00 AM-09:00 AM IST

31 DEC
ASP.NET MVC with AngularJS Development (offline)

09:30 AM-11:00 AM

31 DEC
Angular2 and Angular1 Development (offline)

11:00 AM-12:30 AM

26 NOV
ASP.NET MVC with AngularJS Development (offline)

03:00 PM-04:30 PM

5 NOV
ASP.NET MVC with AngularJS Development (offline)

08:00 AM-09:30 AM

4 SEP
MEAN Stack 2 Development (offline)

11:00 AM-12:30 PM.

BROWSE BY CATEGORY
 
RECENT ARTICLES
SUBSCRIBE TO LATEST NEWS
 
LIKE US ON FACEBOOK
 

Professional Speaks

+