How to use GROUP BY clause in SQL

In this SQL article, we will learn about the GROUP BY clause and how to use it in SQL. We will also discuss using the GROUP BY clause with the WHERE clause.

What is the GROUP BY clause?

The GROUP BY clause is an SQL clause used in the SELECT statement to manage the same records of a column in the group using SQL functions.

Syntax of GROUP BY clause:

SELECT columnname1, columnname2, columnname3 FROM tablename GROUP BY columnname;

We can use multiple columns from the table in the GROUP BY clause.

There are some steps, we have to learn for how to use the GROUP BY clause in the SQL query:

1. Create a new database or use an existing database by selecting the database using the USE keyword followed by the database name.

2. Create a new table inside the selected database, or you can use an already created table.

3. If the table is newly created, insert the records in the newly created database using the INSERT query and view the inserted data using the SELECT query without the GROUP BY clause.

4. Now, we are ready to use the GROUP BY clause in the SQL queries.

Step 1: Create a new database or use an already created database.

I have already created a database. I will use my existing created database name, Company.

USE Company;

Company is the database name.

Those who didn’t have created a database follow the below query to create the database:

CREATE DATABASE database_name;

After creating the database, select the database using the USE keyword followed by the database name.

Step 2: Create a new table or use already existing table:

I have already created a table. I will use the existing table named Employees.

To create the new tables, follow the below CREATE TABLE syntax:

CREATE TABLE table_name(

columnname1 datatype(column size),

columnname2 datatype(column size),

columnname3 datatype(column size)

);

Step 3: Insert the records in the newly created table using the INSERT query and view the records using the SELECT query.

Use the following syntax to insert new records in the table:

INSERT INTO table_name VALUES(value1, value2, value3);

Use the following syntax to view the records from the table:

SELECT * FROM table_name;

The following query will display the records of Employees:

SELECT * FROM Employees;

The output of the above SELECT query is:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
1002	VAIBHAV	SHARMA	60000	NOIDA	C#	5
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	5
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	4
5002	SANKET	CHAUHAN	70000	HYDERABAD	JAVA	3
5003	ROSHAN	NEHTE	48500	CHANDIGARH	C#	5
6001	RAHUL	NIKAM	54500	BANGALORE	TESTING	4
6002	ATISH	JADHAV	60500	BANGALORE	C#	5
6003	NIKITA	INGALE	65000	HYDERABAD	ORACLE	1

Step 4: We are ready to use the GROUP BY clause in the queries

We will now take deep dive into the GROUP BY clause with the help of examples

Example 1: Write a query to display the employee records group by city.

SELECT * FROM EMPLOYEES GROUP BY CITY;

The above query displays the employees' records where an employee from the same city will be considered one group. For example, if there are 10 employees records in table where 3 are from Pune city, 3 are from Mumbai city, 2 are from Hyderabad and Bangalore, then the above query will group Pune city employee Mumbai city employee as one record, and so on.

The output of the above query:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
6001	RAHUL	NIKAM	54500	BANGALORE	TESTING	4
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
5002	SANKET	CHAUHAN	70000	HYDERABAD	JAVA	3
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	5
1002	VAIBHAV	SHARMA	60000	NOIDA	C#	5
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1

As we can see that the employees' records are grouped by city, and records are displayed in the ascending order by default.  

Example 2: Write a query to display employees' records group by salary in descending order.

SELECT * FROM EMPLOYEES GROUP BY SALARY DESC; 

The above query displays the employees' records where employees with the same salary will be considered one group, and records will display in descending order.

The output of the above query:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
5002	SANKET	CHAUHAN	70000	HYDERABAD	JAVA	3
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
6003	NIKITA	INGALE	65000	HYDERABAD	ORACLE	1
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4
1002	VAIBHAV	SHARMA	60000	NOIDA	C#	5
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	5
5003	ROSHAN	NEHTE	48500	CHANDIGARH	C#	5

As we can see, the employees' records are grouped by salary, and records are displayed in descending order, as we mention desc at the end.

Example 3: Write a query to display employees’ records group by salary and city.

SELECT * FROM EMPLOYEES GROUP BY SALARY, CITY;

The above query displays the employees' records where employees with the same salary and city will be considered one group.

For example, suppose the table had 10 employee records. From 10 employees 2 employees' salary and city matches with other two employees and rest six employees salary and city is unmatched then the 6 employees will be considered as 6 separate groups, and 2 employees who match with other 2 employees will be considered as one group. In short, 8 groups will be formed.

The output of the above query:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
5003	ROSHAN	NEHTE	48500	CHANDIGARH	C#	5
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	5
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
6001	RAHUL	NIKAM	54500	BANGALORE	TESTING	4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	4
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
1002	VAIBHAV	SHARMA	60000	NOIDA	C#	5
6002	ATISH	JADHAV	60500	BANGALORE	C#	5
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4
6003	NIKITA	INGALE	65000	HYDERABAD	ORACLE	1
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
5002	SANKET	CHAUHAN	70000	HYDERABAD	JAVA	3

As we can see, the employees' records are grouped by salary and city, and records are displayed in ascending order by default. 

Example 4: Write a query to display employees' records by city and department.

SELECT * FROM EMPLOYEES GROUP BY CITY, DEPARTMENT;

The above query displays the employees' records where employees are in the same city, and the department will be considered one group.

The output of the above query:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
6002	ATISH	JADHAV	60500	BANGALORE	C#	5
6001	RAHUL	NIKAM	54500	BANGALORE	TESTING	4
5003	ROSHAN	NEHTE	48500	CHANDIGARH	C#	5
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	4
5002	SANKET	CHAUHAN	70000	HYDERABAD	JAVA	3
6003	NIKITA	INGALE	65000	HYDERABAD	ORACLE	1
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	5
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4
1002	VAIBHAV	SHARMA	60000	NOIDA	C#	5
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1

As we can see, the employees' records are grouped by city and department, and records are displayed in ascending order by default. 

Example 5: Write a query to count the list of employees in each department from the employees' table.

SELECT DEPARTMENT, COUNT(DEPARTMENT) FROM EMPLOYEES GROUP BY DEPARTMENT;

The above query displays the count of employees in each department group by the department. Like Six employees work in the HR department, five work in another department.

The output of the above query:

DEPARTMENT	COUNT(DEPARTMENT)
C#	4
FMW	3
JAVA	4
ORACLE	4
TESTING	3

As we can see, four employees work in C# Department, three work in FMW Department, etc.

Example 6: Write a query to count the list of employees from each city from the employees' table.

SELECT CITY, COUNT(CITY) FROM EMPLOYEES GROUP BY CITY;

The above query displays the count of employees in each city group by city. Like three employees work from Pune city, four work from another city, and so on.

The output of the above query:

CITY	COUNT(CITY)
BANGALORE	2
CHANDIGARH	2
DELHI	2
HYDERABAD	2
JAIPUR	2
MUMBAI	3
NOIDA	2
PUNE	3

As we can see, two employees work from Bangalore city, three works from Mumbai city, and so on.

Example 7: Write a query to sum the employee salary group by the city.

SELECT CITY, SUM(SALARY) AS SALARY FROM EMPLOYEES GROUP BY CITY;

The above is used to sum the employees' salaries grouped by the city name. For example, for employees from the same city, their salary will be the sum and considered one group. We used the aggregate sum function followed by the salary column for adding salary.

The output of the above query:

CITY	SALARY
BANGALORE	115000
CHANDIGARH	104000
DELHI	115500
HYDERABAD	135000
JAIPUR	101000
MUMBAI	169000
NOIDA	114500
PUNE	186500

As we can see, Bangalore city sum salary is 115000, Chandigarh city sum salary is 104000 which is the addition of different employee salary, but from the city, the same approach is used for each city.

Example 8: Write a query to find the minimum salary from each department.

SELECT DEPARTMENT, MIN(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT;

The above query is used to find the employee's minimum salary from each department. One of the employees from the Java department's salary is 54500, which is the lowest in over the entire java department. The same 48500 is the lowest salary paid to the employee in the C# department.

The output of the above query:

DEPARTMENT	MIN(SALARY)
C#	48500
FMW	50500
JAVA	54500
ORACLE	55500
TESTING	54500

As we can see, 50500 is the lowest salary paid to one of the employees in the FMW department, 55500 is the lowest salary paid to one of the employees in the ORACLE department.

Example 9: Write a query to find the minimum salary from each city.

SELECT CITY, MAX(SALARY) FROM EMPLOYEES GROUP BY CITY;

The above query is used to find the maximum salary from each city. One of the employees from the Pune city salary is 65500 which is the highest in over the entire Pune city, same 60500 is the highest salary paid to the employee in the Mumbai city.

The output of the above query:

CITY	MAX(SALARY)
BANGALORE	60500
CHANDIGARH	55500
DELHI	60000
HYDERABAD	70000
JAIPUR	50500
MUMBAI	60500
NOIDA	60000
PUNE	65500

As we can see, 50500 is the highest salary paid to one of the employees in Jaipur city, 55500 is the highest salary paid to one of the employees in Chandigarh city.