In this tutorial, we will learn how to use GROUP BY clause and grouping row based on columns value.


  • SQL GROUP BY Clause is used to divide the records or rows of the table into smaller groups and it is used with the SELECT statement.
  • SQL GROUP BY Clause is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns and get the summary information of every group.
  • SQL GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
Syntax - GROUP BY Clause
SELECT column1,column2,...columnN
FROM tableName
GROUP BY column(s)
ORDER BY column(s);


Let us consider three tables "Employee" and "Department"  for records 

Table Name: Employee

ID EmpName City Country Gender Salary DeptID
1 Shankar Delhi India male 25000 1
2 Sourabh Delhi India male 30000 1
3 Ranvijay Mumbai India male 15000 2
4 Kapil Noida India male 25000 3
5 Shalini Jaipur India female 18000 2
6 Rakesh Faridabad India male 23000 1
7 Akshay Mumbai India male 21000 2
8 Sarah New York US female 76000 3
9 Rocky Noida India male 28000 3

Table: Department

DeptID DepartmentName
1 IT
2 Finance
3 HR

Example of GROUP BY Clause

Finding the number of the employee by City using GROUP BY clause, a query will be:

Example - GROUP BY Clause
SELECT COUNT(ID) as 'No. of Employee by City',City
FROM Employee
No. of Employee by City City
2 Delhi
1 Faridabad
1 Jaipur
2 Mumbai
1 New York
2 Noida

For finding the number of the employee in each department using GROUP BY clause with JOIN, a query will be:

Example - GROUP BY Clause With JOIN Clause
SELECT Dept.DepartmentName,COUNT(Emp.DeptId) AS 'No. of Employee in Each Dept.'
FROM Employee AS Emp
JOIN Department AS Dept ON Emp.DeptId=Dept.DeptID
GROUP BY Dept.DepartmentName,Emp.DeptID;
DepartmentName No. of Employee in Each Dept.
IT 3
Finance 2
HR 2