SQL Reference - GROUP BY

Example

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Output

| department | COUNT(*) |
|------------|----------|
| Finance    | 100      |
| HR         | 50       |
| IT         | 150      |

Explanation

In this example, the SQL statement groups the ‘employees’ table by the ‘department’ column, and it counts the number of records for each group. The output table therefore shows the number of employees in each department.

Example

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Output

department	count
Sales	100
HR	50
IT	150

Explanation

The above SQL statement groups records by the ‘department’ field in the ‘employees’ table and counts the number of records in each grouped category. The result displays the number of employees in each department.

Example

SELECT Department, COUNT(EmployeeID) as 'Number of Employees'
FROM Employees
GROUP BY Department

Output

Department   | Number of Employees
-------------| -------------------
Marketing    | 10
IT           | 15
HR           | 8
Sales        | 12

Explanation

The above SQL statement is grouping the rows from the “Employees” table by the “Department” column. The COUNT(EmployeeID) function is then used to count the number of employee IDs that appear within each department, effectively providing the total number of employees in each department. The as 'Number of Employees' is used to alias the output column name to “Number of Employees”.

Example

SELECT department_id, COUNT(*) FROM employees
GROUP BY department_id

Output

DEPARTMENT_ID    COUNT(*)
-------------    --------
10               1
20               2
30               6
40               1
50               45
.
.
.

Explanation

In the provided SQL statement, the GROUP BY clause is used to aggregate rows from the ‘employees’ table that have the same values in the ‘department_id’ column into groups. The COUNT(*) function then counts the number of rows in each group. The output table shows the number of employees in each department.

Example

SELECT Country, COUNT(*)
FROM Customers
GROUP BY Country;

Output

| Country     | COUNT(*) |
|-------------|----------|
| Germany     | 11       |
| Mexico      | 5        |
| USA         | 13       |
| Canada      | 8        |

Explanation

In the above example, GROUP BY statement is used to group the customers based on their country. The COUNT(*) function then counts the number of customers from each country.