SQL GROUP BY Clause

The GROUP BY clause is used to group rows together by common column values.

The GROUP BY clause is used in conjunction with the SELECT statement.

The GROUP BY clause is often used with aggregate functions (MIN, MAX, COUNT, AVG, SUM).

Syntax

The basic syntax of GROUP BY can be given with:

SELECT column1_name, column2_name, ...columnN_name, aggregate_function (column_name)
FROM table_name
[WHERE condictions]
GROUP BY column1_name, column2_name, ...columnN_name
[ORDER BY column_name];

Parameters:

• colunm1_name, column2_name, ... columnN_name: Columns that are not encapsulated within an aggregate function and must be included in the GROUP BY clause.
• aggregate_function: This is an aggregate function like MIN, MAX, COUNT, AVG, SUM

Note: The GROUP BY clause must appear before the ORDER BY, and after the FROM and WHERE clauses.

Demo Table

To understand more the GROUP BY clause, let us suppose that we have a table called "Employee" with the following records:

SQL GROUP BY - Example

The following SQL statement will find the total number of employees in every department.

SELECT dept_name as "Department", COUNT(emp_no) AS "Number of Employees"
FROM employee
GROUP BY detp_name;

After the execution, the result-set will be as follows:

+------------------+---------------------+
| Department       | Number of Employees |
+------------------+---------------------+
| Customer Service | 1                   |
| Development      | 2                   |
| Finance          | 2                   |
| Human Resources  | 1                   |
| Marketing        | 2                   |
+------------------+---------------------+