Summary -
In this topic, we described about the GROUP BY Clause with detailed example.
GROUP BY clause is used to group the rows with matching values using the specified column.
GROUP BY clause is used with aggregate functions like COUNT( ), MAX( ).
Syntax -
SELECT column1, function_name (column2), …, columnN
FROM table_name
[ WHERE condition]
GROUP BY column1, column2
[ORDER BY column1, column2];
- column1, column2, …, columnN - Specifies the name of the columns that are used to fetch data.
- function_name – The aggregate function like count( ), sum( ) that are to fetch data.
- table_name – Specifies the name of the table.
Example -
Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.
employee_details -
| emp_id | emp_name | designation | manager_id | date_of_hire | salary | dept_id |
|---|---|---|---|---|---|---|
| 001 | Employee1 | Director | 2019-07-11 | 45000.00 | 1000 | |
| 002 | Employee2 | Director | 2019-07-11 | 40000.00 | 2000 | |
| 003 | Employee3 | Manager | Employee1 | 2019-07-11 | 27000.00 | 1000 |
| 004 | Employee4 | Manager | Employee2 | 2019-10-08 | 25000.00 | 2000 |
| 005 | Employee5 | Analyst | Employee3 | 2019-07-11 | 20000.00 | 1000 |
| 006 | Employee6 | Analyst | Employee3 | 2019-10-08 | 18000.00 | 1000 |
| 007 | Employee7 | Clerk | Employee3 | 2019-07-11 | 15000.00 | 1000 |
| 008 | Employee8 | Salesman | Employee4 | 2019-09-09 | 14000.00 | 2000 |
| 009 | Employee9 | Salesman | Employee4 | 2019-10-08 | 13000.00 | 2000 |
Scenario – Fetch the count using GROUP BY.
Requirement – Fetching total employees in all departments from the employee_details table. The query was as follows –
SELECT COUNT( emp_id ) AS 'Total employees', dept_id
FROM employee_details GROUP BY dept_id;
By executing above query, we can get results as shown below –
| Total employees | dept_id |
|---|---|
| 5 | 1000 |
| 4 | 2000 |
Scenario- Fetch the count using GROUP BY and ORDER BY.
Requirement – Fetching total employees in all departments in department descending order from the employee_details table. The query was as follows –
SELECT COUNT( emp_id ) AS 'total employees',dept_id FROM employee_details
GROUP BY dept_id ORDER BY dept_id DESC;
By executing above query, we can get results as shown below -
| Total employees | dept_id |
|---|---|
| 4 | 2000 |
| 5 | 1000 |
Scenario – Fetch sum by using GROUP BY.
Requirement – Fetching total salary in department 1000 from the employee_details table.
The query was as follows –
SELECT SUM( salary) AS 'Total salary', dept_id
FROM employee_details
WHERE dept_id = 1000 GROUP BY dept_id;
By executing above query, we can get results as shown below -
| Total salary | dept_id |
|---|---|
| 125000.00 | 1000 |
Scenario – Get minimum using GROUP BY.
Requirement – Get minimum salary in each dept_id. The query was as follows –
SELECT MIN(salary) as 'lowest salary', dept_id
FROM employee_details group by dept_id;
By executing above query, we can get results as shown below –
| lowest salary | dept_id |
|---|---|
| 15000.00 | 1000 |
| 13000.00 | 2000 |
Scenario – Get maximum using GROUP BY.
Requirement – Fetch maximum salary in each department. The query was as follows –
SELECT MAX(salary) as 'highest salary', dept_id
FROM employee_details GROUP BY dept_id;
By executing above query, we can get results as shown below –
| highest salary | dept_id |
|---|---|
| 45000.00 | 1000 |
| 40000.00 | 2000 |