How to Use GROUP BY in SQL

In this SQL section, you will learn what the GROUP BY keyword is and how to implement it in Structured Query Language. We will also discuss how to use GROUP BY clause with WHERE clause.

What is GROUP BY?

GROUP BY is an SQL keyword used in the SELECT query for arranging the same values of a column in the group by using SQL functions.

Syntax of GROUP BY clause

SELECT Column_Name_1, Column_Name_2, ........, Column_Name_N FROM Table_Name GROUP BY Column_Name_1, Column_Name_2, ........, Column_Name_N;

We can use more than one field of the table in the GROUP BY clause. We have to separate the name of multiple columns by a comma.

Follow the steps given below to learn how to use the GROUP BY clause in the SQL table:

Create a Simple Database and Table.
Insert Data into the Table
View the Inserted Data without the GROUP BY clause.
Use the GROUP BY clause.

Step 1: Create a simple Database and Table

First, you have to create a new database in SQL.

The following query creates the Hospital Database:

CREATE Database Hospital;

Now, you have to create the new table using the following CREATE TABLE syntax:

CREATE TABLE table_name
(
column_Name_1 data type (size of the column_1),
column_Name_2 data type (size of the column_2),
column_Name_3 data type (size of the column_3),
...
column_Name_N data type (size of the column_1)
);

The following query creates the Doctor_Info table in the Hospital Database:

CREATE TABLE Doctor_Info
(
Doctor_ID Int PRIMARY KEY,
Doctor_Name VARCHAR (100),
Doctor_Specialist VARCHAR (80),
Doctor_GenderVarchar (20),
Doctor_Country Varchar (80)
) ;

Step 2: Insert the Data into the table

Now, you have to insert the data in the table using the following syntax:

INSERT INTO <Table_Name> (Column_Name_1, Column_Name_2, Column_Name_3, ......., Column_Name_N) VALUES (value_1 of Column_1, value_2, value_3, ...., value_N);

The following query inserts the record of those doctors who work in Hospital:

INSERT INTO Doctor_Info (Doctor_ID, Doctor_Name, Doctor_Specialist, Doctor_Gender, Doctor_Country) VALUES ( 1035, Jones, Malaria_Specialist, Male, United Kingdom),
(1015, Marry, Diabties_Specialist, Female, United States),
(1003, Harry, Fever_Specialist, Male, United Kingdom),
(1044, Ella, Cancer_Specialist, Female, United States),
(1025, Moria, Corona_Specialist, Female, Europe);

Step 3: View the Inserted data of Table Without GROUP BY

The following query shows the record of Doctors in an unsorted manner:

SELECT * FROM Doctor_Info;

The output of the above SELECT query is shown below:

Doctor_ID	Doctor_Name	Doctor_Disease	Doctor_Gender	Doctor_Country
1035	Jones	Malaria_Specialist	Male	United Kingdom
1015	Marry	Diabities_Specialist	Female	United State
1003	Harry	Fever_Specialist	Male	United Kingdom
1044	Ella	Cancer_Specialist	Female	United State
1025	Moria	Corona_Specialist	Female	Europe

Step 4: Use GROUP BY clause

The following SQL query uses the GROUP BY keyword to list the number of doctors in each country:

SELECT COUNT (Doctor_ID), Doctor_Country GROUP BY Doctor_Country;

The output of the above SELECT with ODER BY query is shown in the following Doctor table:

Output:

How to Use GROUP BY in SQL

GROUP BY with SQL ORDER BY clause

We can also use the ORDER BY keyword with the GROUP BY clause in the SQL SELECT statement.

Syntax of GROUP BY clause with ORDER BY clause

SELECT Function_Name (Column_Name) FROM Table_Name GROUP BY Column_Name ORDER BY Function_Name (Column_Name);

Example of GROUP BY clause with ORDER BY clause

The following query creates the new Subject table in the School database:

CREATE TABLE Subject
(
Subject_ID INT PRIMARY KEY,
Subject_Name VARCHAR (50),
Subject_Teacher VARCHAR (70),
Student_ID INT
) ;

The following INSERT INTO query inserts the records into the Subject table:

INSERT INTO Subject(Subject_ID, Subject_Name, Subject_Teacher, Student_ID) VALUES (2211, Maths, Ramesh, 101),
(2252, English, Somya, 103),
(2201, Chemistry, Suresh, 101),
(2224, Physics, Aman, 103),
(2248, Computer, Bhanu, 101),
(2208, Hindi, Sonu, 104),
(2221, Biology, Punit, 104));

The following query uses the SQL ORDER BY clause with GROUP BY:

SELECT Count(Subject_ID), Student_ID FROM Subject GROUP BY Student_ID ORDER BY COUNT(Subject_ID) DESC;

Output:

How to Use GROUP BY in SQL

GROUP BY clause with MIN function

We can also use the MIN aggregate function with the GROUP BY clause in Structured Query Language.

Syntax of Group BY clause with MIN function:

SELECT Column_Name_1, MIN(Column_Name) FROM Table_Name GROUP BY Column_Name_1;

Example of MIN Aggregate Function with GROUP BY Clause

This example takes the below School_Stu_Details table to understand the concept of GROUP BY clause with MIN aggregate function:

CREATE TABLE School_Stu_Details
(
Stu_ID INT NOT NULL,
Stu_Name varchar(100),
Stu_Subject varchar(50),
Stu_Age INT,
Stu_Marks INT
);

The following INSERT INTO statements insert the record of School students:

INSERT INTO School_Stu_Details VALUES (101, Anuj, English, 20, 88);
INSERT INTO School_Stu_Details VALUES (102, Raman, Maths, 24, 98);
INSERT INTO School_Stu_Details VALUES (104, Shyam, Hindi, 19, 92);
INSERT INTO School_Stu_Details VALUES (107, Vikash, Computer, 20, 78);
INSERT INTO School_Stu_Details VALUES (111, Monu, English, 21, 65);
INSERT INTO School_Stu_Details VALUES (114, Jones, Hindi, 18, 93);
INSERT INTO School_Stu_Details VALUES (121, Parul, Maths, 20, 97);
INSERT INTO School_Stu_Details VALUES (123, Divya, English, 21, 89);
INSERT INTO School_Stu_Details VALUES (128, Hemant, Computer, 23, 90);
INSERT INTO School_Stu_Details VALUES (130, Nidhi, Hindi, 20, 88);
INSERT INTO School_Stu_Details VALUES (132, Priya, English, 22, 99);
INSERT INTO School_Stu_Details VALUES (138, Mohit, Maths, 21, 92);

The following query simply shows the record of students in the tabular form:

SELECT * FROM School_Stu_Details;

Stu_ID	Stu_Name	Stu_Subject	Stu_Age	Stu_Marks
101	Anuj	English	20	88
102	Raman	Maths	24	98
104	Shyam	Hindi	19	92
107	Vikash	Computer	20	78
111	Monu	English	21	65
114	Jones	Hindi	18	93
121	Parul	Maths	20	97
123	Divya	English	21	89
128	Hemant	Computer	23	90
130	Nidhi	Hindi	20	88
132	Priya	English	22	99
138	Mohit	Maths	21	92

The following query shows the minimum marks of a student in each subject from the above School_Stu_Details table:

SELECT Stu_Subject, MIN (Stu_Marks) FROM School_Stu_Details GROUP BY Stu_Subject;

Output:

Stu_Subject	MIN (Stu Marks)
English	65
Maths	92
Hindi	88
Computer	78

GROUP BY clause with MAX function

We can also use the MAX aggregate function with the GROUP BY clause in Structured Query Language.

Syntax of Group BY clause with MAX aggregate function:

SELECT Column_Name_1, MAX(Column_Name) FROM Table_Name GROUP BY Column_Name_1;

Example of MAX aggregate Function with GROUP BY Clause

This example takes the below School_Stu_Details table to understand the concept of GROUP BY clause with SQL MAX aggregate function:

CREATE TABLE School_Stu_Details
(
Stu_ID INT NOT NULL,
Stu_Name varchar(100),
Stu_Subject varchar(50),
Stu_Age INT,
Stu_Marks INT
);

The following INSERT INTO statements insert the record of School students:

The following query simply shows the record of students in the tabular form:

SELECT * FROM School_Stu_Details;

Stu_ID	Stu_Name	Stu_Subject	Stu_Age	Stu_Marks
101	Anuj	English	20	88
102	Raman	Maths	24	98
104	Shyam	Hindi	19	92
107	Vikash	Computer	20	78
111	Monu	English	21	65
114	Jones	Hindi	18	93
121	Parul	Maths	20	97
123	Divya	English	21	89
128	Hemant	Computer	23	90
130	Nidhi	Hindi	20	88
132	Priya	English	22	99
138	Mohit	Maths	21	92

The following query shows the maximum marks of a student in each subject from the above School_Stu_Details table:

SELECT Stu_Subject, MAX (Stu_Marks) FROM School_Stu_Details GROUP BY Stu_Subject;

Output:

Stu_Subject	MAX (Stu Marks)
English	99
Maths	98
Hindi	93
Computer	90

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz