SQL - GROUP BY Clause
The GROUP BY clause is used to get the summary data based on one or more groups. The groups can be formed on one or more columns. For example, the GROUP BY query will be used to count the number of employees in each department, or to get the department wise total salaries.
You must use the aggregate functions such as
AVG(), etc., in the SELECT query.
The result of the GROUP BY clause returns a single row for each value of the GROUP BY column.
SELECT column1, column2,...columnN FROM table_name [WHERE] [GROUP BY column1, column2...columnN] [HAVING] [ORDER BY]
The SELECT clause can include columns that are used with the GROUP BY clause. So, to include other columns in the SELECT clause, use the aggregate functions like
AVG() with those columns.
- The GROUP BY clause is used to form the groups of records.
- The GROUP BY clause must come after the WHERE clause if present and before the HAVING clause.
- The GROUP BY clause can include one or more columns to form one or more groups based on that columns.
- Only the GROUP BY columns can be included in the SELECT clause. To use other columns in the SELECT clause, use the aggregate functions with them.
For the demo purpose, we will use the following
Department tables in all examples.
|4||'Lex'||'De Haan'||'[email protected]'||15000||1|
Consider the following GROUP BY query.
SELECT DeptId, COUNT(EmpId) as 'Number of Employees' FROM Employee GROUP BY DeptId; --following query will return same data as above SELECT DeptId, COUNT(*) as 'No of Employees' FROM Employee GROUP BY DeptId;
The above query includes the GROUP BY
DeptId clause, so you can include only
DeptId in the SELECT clause. You need to use aggregate functions to include other columns in the SELECT clause, so
COUNT(EmpId) is included because we want to count the number of employees in the same
'No of Employees' is an alias of the
COUNT(EmpId) column. The query will display the following result.
|DeptId||No of Employees|
The following query gets the department name instead of
DeptId in the result.
SELECT dept.Name as 'Department', count(emp.empid) as 'No of Employees' FROM Employee emp, Department dept WHERE emp.deptid = dept.DeptId GROUP by dept.Name
|Department||No of Employees|
In the same way, the following query gets the department-wise total salaries.
SELECT dept.Name, sum(emp.salary) as 'Total Salaries' FROM Employee emp, Department dept WHERE emp.deptid = dept.DeptId GROUP by dept.Name
The following query would throw an error, because
dept.Name is not included in the GROUP BY clause, or no aggregate function is used.
SELECT dept.Name, sum(emp.salary) as 'Total Salaries' FROM Employee emp, Department dept WHERE emp.deptid = dept.DeptId GROUP by dept.DeptId