Definition, Use of Group by and Having Clause

Definition, Use of Group by and Having Clause: An Overview

In SQL Server Tutorial, we have group by clause for grouping the records of the database table according to our needs. We use having clause to filter data that we get from the group by clause. Having clause operates only on group by clause means to use having clause we need to use group by clause first. Let's go through both clauses in detail in SQL Certification Training.

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 the Select statement. We can use group by clause without the where clause.

Syntax of Group By Clause

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

Example

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 

This SQL code generates a table called StudentMarks that contains columns for the student's roll number, name, subject, and marks. It then enters data for three students from different subjects and displays the full table with a SELECT statement.

-- Group By clause without where condition
SELECT st_Name, SUM(st_Marks) AS 'Total Marks'
FROM StudentMarks
GROUP BY st_Name; 

This SQL query generates the total marks for each student by grouping the data by student name without using any filtering conditions and displays the sum of marks for each unique student name in the StudentMarks table.

-- 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; 

This SQL statement computes the total marks for the student 'Mohan' by filtering the data with a WHERE clause before applying the GROUP BY clause, and then displays the sum of marks for the student 'Mohan' in the StudentMarks table.

-- 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; 

This SQL query uses the GROUP BY clause to find the top marks in each subject group from the StudentMarks dataset.

Group By Clause Advantages

• It enables the grouping of rows with identical values in one or more columns.
• It contributes to reducing the amount of rows in the output table and summarising data.
• It can be combined with aggregate functions like SUM, COUNT, AVG, MIN, MAX, and so on to provide summary statistics for each group.
• It supports grouping based on several columns.

Group By Clause Disadvantages

• Writing and optimizing complex GROUP BY queries can be time-consuming.
• Some sorts of aggregations may necessitate the use of subqueries or temporary tables.
• The results of a complex GROUP BY query can be difficult to understand.

Having Clause

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

Example

-- 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 

This SQL query uses the HAVING clause after grouping student names to filter and display only students whose total marks exceed 205, resulting in a summary of students who scored more than 205 marks from the StudentMarks dataset.

-- 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 

This SQL query uses a WHERE clause to include only students with roll numbers between 1 and 3, then groups them by name and displays the total marks for students whose total marks exceed 205, resulting in a summary of students within the specified roll number range who scored more than 205 marks from the StudentMarks table.

Having Clause Advantages

• Enables group filtering using an aggregate function.
• It can be used to conduct computations on aggregated data, such as percentages and ratios.
• It can be combined with complicated searches to produce more precise results.

Having Clause Disadvantages

• Slower query performance with complex calculations.
• A complex HAVING query's output can be difficult to understand.
• Certain filtering techniques may necessitate the use of subqueries or temporary tables.

Similarities Between Having Clause & Group by Clause

• Data is summarised in SQL queries using the GROUP BY and HAVING clauses.
• They are used in conjunction to organize and filter data depending on summary results.
• They can be used to do calculations with aggregated data.

Difference between Having Clause and Group by Clause

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 the 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 the 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, questions, or comments about this article.

Take our free sqlserver skill challenge to evaluate your skill

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET CHALLENGE