SQL GROUP BY CLAUSE

The GROUP BY clause is used to arrange similar records into the groups in the Structured Query Language queries. The records are arranged with the help of functions which is equivalent to each other.

We can use the GROUP BY clause with the SELECT query in the Structured Query Language. We placed the GROUP BY clause after the WHERE clause and before the ORDER BY clause in the SQL queries.

The syntax for the SQL GROUP BY clause is:

SELECT  * FROM Table_Name GROUP BY Column_Name;

The above syntax will select all the data or records from the table name mentioned, and it will arrange all those data or records in the group by column name.

The syntax for SQL GROUP BY clause with a specific column is given below:

SELECT Column_Name_1, Column_Name_2, Column_Name_3, Column_Name__4, Column_Name__5 FROM Table_Name GROUP BY Column_Name;

The above syntax will display records of selective columns only and arrange those column data in the group.

The GROUP BY clause specially used with the aggregate function i.e. max(), min(), avg(), sum(), count() to group result by one or more than one columns.

The below syntax is used by the SQL GROUP BY clause using Aggregate Functions is as follows:

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

Let’s understand the SQL GROUP BY Clause with the help of an example.

Consider the already existing table with the following data:

Table Name: Employee_Details

E_Id	E_Name	E_Salary	E_City	Designation	Age
1001	Kirti Kirtane	60000	Mumbai	Project Manager	26
1002	Akash Vani	40000	Pune	System Engineer	22
1003	Anupam Mishra	55000	Hyderabad	Project Manager	25
1004	Anuj Rawat	45000	Hyderabad	Software Tester	25
1005	Akanksha Yadav	42000	Pune	Associate Software Developer	23
1006	Bhavesh Wani	50000	Mumbai	Associate Software Developer	24
2001	Sakshi Sharma	40000	Bangalore	System Engineer	23
2002	Megha Ghatole	45000	Bangalore	Software Tester	24
2003	Surbhi Nahar	60000	Pune	Project Manager	26
2004	Supriya Shende	55000	Mumbai	Software Developer	25
2005	Prachi Sharma	52000	Hyderabad	Software Developer	24
2006	Purva Dhandekar	50000	Bangalore	Software Tester	23
3001	Shruti Deshpande	60000	Pune	Project Manager	26
3002	Rohit Nikam	40000	Hyderabad	System Engineer	23
3003	Sahil Jain	50000	Mumbai	Software Developer	24

Example 1: Write a query to display employee’s details group by age column

SELECT * FROM Employee_Details GROUP BY Age;

The above query will display all the records of the Employee_Details table group by age column.

The output of the above query is as follows:

E_Id	E_Name	E_Salary	E_City	Designation	Age
1002	Akash Vani	40000	Pune	System Engineer	22
1005	Akanksha Yadav	42000	Pune	Associate Software Developer	23
1006	Bhavesh Wani	50000	Mumbai	Associate Software Developer	24
1003	Anupam Mishra	55000	Hyderabad	Project Manager	25
1001	Kirti Kirtane	60000	Mumbai	Project Manager	26

Example 2: Write a query to display employee’s details group by Salary, and Designation column

SELECT * FROM Employee_Details GROUP BY E_Salary, Designation;

The above query will display all the records of the Employee_Details table group by Salary and Designation column.

The output of the above query is as follows:

E_Id	E_Name	E_Salary	E_City	Designation	Age
1002	Akash Vani	40000	Pune	System Engineer	22
1005	Akanksha Yadav	42000	Pune	Associate Software Developer	23
1004	Anuj Rawat	45000	Hyderabad	Software Tester	25
1006	Bhavesh Wani	50000	Mumbai	Associate Software Developer	24
3003	Sahil Jain	50000	Mumbai	Software Developer	24
2006	Purva Dhandekar	50000	Bangalore	Software Tester	23
2005	Prachi Sharma	52000	Hyderabad	Software Developer	24
1003	Anupam Mishra	55000	Hyderabad	Project Manager	25
2004	Supriya Shende	55000	Mumbai	Software Developer	25
1001	Kirti Kirtane	60000	Mumbai	Project Manager	26

Example 3: Write a query to display employee’s details group by Salary column in the ascending order by Employee name.

SELECT * FROM Employee_Details GROUP BY E_Salary ORDER BY E_Name;

The above query will display all the records of the Employee_Details table group by Salary column in the ascending order by employee name.

The output of the above query is:

E_Id	E_Name	E_Salary	E_City	Designation	Age
1005	Akanksha Yadav	42000	Pune	Associate Software Developer	23
1002	Akash Vani	40000	Pune	System Engineer	22
1004	Anuj Rawat	45000	Hyderabad	Software Tester	25
1003	Anupam Mishra	55000	Hyderabad	Project Manager	25
1006	Bhavesh Wani	50000	Mumbai	Associate Software Developer	24
1001	Kirti Kirtane	60000	Mumbai	Project Manager	26
2005	Prachi Sharma	52000	Hyderabad	Software Developer	24

Now, let’s understand the GROUP BY clause using the aggregate function with the help of an example

Consider the already existing table with the following data:

Table Name:  Diploma_Students

Student_Id	Student_Name	First_Sem	Second_Sem	Third_Sem	Fourth_Sem	Fifth_Sem	Sixth_Sem	Total	Department_Id
202111	Vaishnavi Patil	94	91	88	85	95	92	91	6
202112	Vaibhav Lokhande	85	90	92	80	85	82	86	2
202113	Yash Dhull	90	88	94	87	85	90	89	3
202114	Sonali Patole	95	90	92	88	92	90	91	4
202115	Axar Patel	85	80	82	86	92	84	85	1
202116	Meena Mishra	78	75	80	74	85	77	78	3
202117	Mahesh Kumbhar	75	80	75	78	80	76	77	5
202118	Sakshi Patil	80	78	74	78	80	77	78	2
202119	Sopan Bhore	70	68	75	75	80	80	75	2
202220	Prajwal Lokhande	80	85	85	75	78	80	81	4
202221	Anuja Wanare	85	88	86	82	84	85	85	7
202222	Venkatesh Iyer	90	89	87	90	92	91	90	3
202223	Anushka Sen	70	75	71	74	80	78	75	1
202224	Aakash Jain	80	75	72	74	85	80	78	7
202225	Akshay Agarwal	85	80	78	88	90	82	84	7
202226	Shwetali Bhagwat	90	80	85	88	90	80	86	6
202227	Mayuri Wagh	80	80	85	80	82	85	82	4
202228	Utkarsh Rokade	85	80	80	90	84	84	84	5
202229	Manthan Koli	85	75	84	78	82	80	81	2
202230	Mayur Jain	80	88	87	90	92	90	88	1

Example 1: Write a query to display the total number of student percentages scored in the First-Semester, and Second-Semester column GROUP BY First-Semester, and Second-Semester column

SELECT Count(Student_Name)  AS 'Total Number', First_Sem, Second_Sem FROM Diploma_Student GROUP BY First_Sem, Second_Sem;

The above query displays how many students scored percentages in the first-semester, second-semester GROUP BY first-semester, and second-semester.

The output of the above query is as follows:

Total Number	First_Sem	Second_Sem
1	70	68
1	70	75
1	75	80
1	78	75
1	80	75
1	80	78
1	80	80
1	80	85
1	80	88
1	85	75
3	85	80
1	85	88
1	85	90
1	90	80
1	90	88
1	90	89
1	94	91
1	95	90

Example 2: Write a query to sum the student total column percentage group by Department id.

SELECT SUM(Total) AS 'Total', Department_Id FROM Diploma_Student GROUP BY Department_Id;

We summed the student total column group by department id in the above query.

The output of the above query is as follows:

Total	Department_Id
248	1
320	2
257	3
254	4
161	5
177	6
247	7

Example 3: Write a query to display the maximum total column percentage of the student GROUP BY Department id.

SELECT MAX(Total) as 'Maximum Percentage', Department_Id FROM Diploma_Student GROUP BY Department_Id;

In the above query, we displayed the maximum student percentage of the total column GROUP BY Department Id

The output of the above query is as follows:

Maximum Percentage	Department_Id
88	1
86	2
90	3
91	4
84	5
91	6
85	7

Example 4: Write a query to display the minimum first-semester column percentage of the student GROUP BY Department id.

SELECT MIN(First_Sem) as 'Minimum Percentage', Department_Id FROM Diploma_Student GROUP BY Department_Id;

In the above query, we displayed the minimum student percentage of the first-semester column GROUP BY Department Id

The output of the above query is as follows:

Minimum Percentage	Department_Id
70	1
70	2
78	3
80	4
75	5
90	6
80	7