SQL GROUP BY

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

SQL GROUP BY Clause

• 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.

SELECT column1,column2,...columnN
FROM tableName
Where [CONDITION]
GROUP BY column(s)
ORDER BY column(s);

Example:

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

Table Name: Employee

Table: Department

Example of GROUP BY Clause

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

SELECT COUNT(ID) as 'No. of Employee by City',City
FROM Employee
GROUP BY City;

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

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;