GROUP BY clause in SQL

The GROUP BY clause in SQL is used to group rows that have the same values in specified columns into summary rows. It is often used with aggregate functions such as COUNT, SUM, AVG, MAX, and MIN to perform calculations on each group of rows.

Example: Let’s create an employees table.

SQL

CREATE TABLE Employees (
   EmployeeID INT,
    Name VARCHAR(20),
    Age int,
    Salary VARCHAR(10),
    Department VARCHAR(25),
    Gender VARCHAR(1)
);

Load the data into the Employees table.

SQL

insert into Employees values
(1, ‘Ashish’, 24, ‘25000’, ‘Electrical’, ‘M’), 
(2, ‘Kirshan’, 32, ‘30000’, ‘Electrical’, ‘M’),
(3, ‘Anjali’, 20, ‘25000’, ‘Electronics’, ‘F’),
(4, ‘Manjulika’, 30, ‘15000’, ‘Electrical’, ‘F’),
(5, ‘Katrina’, 37, ‘18000’, ‘Computer’, ‘F’),
(6, ‘Esha’, 37, ‘20000’, ‘Computer’, ‘F’),
(7, ‘Ankita’, 20, ‘9000’, ‘Electronics’, ‘M’), 
(8, ‘Meenakshi’, 30, ‘15000’, ‘Computer’, ‘F’),
(9, ‘Alia’, 37, ‘16000’, ‘Electronics’, ‘F’);

By executing the following query we can see the data in the table:

SQL

SELECT * FROM Employees;

The above query returned the following result:

Table: Employees

EmployeeID	Name	Age	Salary	Department	Gender
1	Ashish	24	25000	Electrical	M
2	Kirshan	32	30000	Electrical	M
3	Anjali	20	25000	Electronics	F
4	Manjulika	30	15000	Electrical	F
5	Katrina	37	18000	Computer	F
6	Esha	37	20000	Computer	F
7	Ankita	20	9000	Electronics	M
8	Meenakshi	30	15000	Computer	F
9	Alia	37	16000	Electronics	F

Count Employees by Department To count the number of employees in each department:

SQL

SELECT department, COUNT(*) AS Num_employees
FROM employees
GROUP BY department;

The above query is returned the following result:

Department	Num_employees
Electrical	3
Electronics	3
Computer	3

This query groups the rows by the department column and counts the number of employees in each group.

Average Salary by Department To find the average salary in each department:

SQL

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

The above query returns the following result:

Department	Avg_salary
Electrical	23333.33
Electronics	16666.67
Computer	17666.67

This query groups the rows by the department column and calculates the average salary for each group.

Total Salary by Department To find the total salary paid to employees in each department:

SQL

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

The above query returns the following result:

Department	Total_salary
Electrical	70000
Electronics	50000
Computer	53000

This query groups the rows by the department column and calculates the total salary for each group.

Maximum Salary by Department To find the maximum salary in each department:

SQL

SELECT department, MAX(salary) AS max_salary
FROM employees
GROUP BY department;

The above query returns the following result:

Department	Max_salary
Electrical	30000
Electronics	9000
Computer	20000

This query groups the rows by the department column and finds the highest salary in each group.

Previous Next