Browse Tutorials
Definition, Use of Group by and Having Clause

Definition, Use of Group by and Having Clause

01 Apr 2024
Intermediate
7.6K Views
11 min read

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

Having ClauseGroup by Clause
It is used to apply additional conditions to the query.The group by clause is used to group data based on a certain column or row.
Having cannot be used without a group by clause; in aggregate functions, it operates similarly to the where clause.The group by function can be used without a clause in the select statement.
The having clause may contain aggregate functions.It cannot have aggregate functions.
It restricts the query output by utilizing certain conditions.It groups the output based on some rows or columns.

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.

FAQs

Q1. What is the objective of the GROUP BY and HAVING clauses in SQL?

The "Group By" clause organizes data into summary rows based on common values, and the "Having" clause filters those groups depending on an aggregated data condition.

Q2. Can we use HAVING in SQL without GROUP BY?

The having clause can also be used with the Transact-SQL extension, which allows you to omit the group by clause from a query with an aggregate in the select list. These scalar aggregate functions calculate values for the entire table rather than individual groups inside it.

Q3. What types of functions can be used with the GROUP BY and HAVING clauses?

An aggregate function in SQL returns a single value after calculating many column values. We frequently combine aggregate functions with the SELECT statement's GROUP BY and HAVING clauses.

Q4. What are the GROUP BY rules in SQL?

The GROUP BY statement aggregates rows with the same values into summary rows, such as "find the number of customers in each country". The GROUP BY statement is frequently combined with aggregate methods (COUNT(), MAX(), MIN(), SUM(), and AVG()) to group the result set by one or more columns.

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

Share Article
Batches Schedule
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this