After this lesson you can build summary tables: employees per department, average salary per job title, hours per project, hires per year. A single aggregate over the whole table answers one question. GROUP BY lets you ask the same question for every category at once.
The examples use the sample company database and were run on SQLite.
Groups and aggregates
In the previous lesson, COUNT(*) gave one number for the whole table. Add GROUP BY and the database first sorts the rows into groups, one for each distinct value in the grouping column, and then runs the aggregate separately inside each group:
SELECT department_id,
COUNT(*) AS people
FROM employees
GROUP BY department_id;+---------------+--------+
| department_id | people |
+---------------+--------+
| NULL | 1 |
| 1 | 6 |
| 2 | 5 |
| 3 | 3 |
| 4 | 2 |
| 5 | 3 |
+---------------+--------+There is one result row for each department number, plus a row for NULL, which belongs to Ravi, the employee without a department. The counts add up to 20, the number of employees.
You can use several aggregates in the same query, and order the result as usual:
SELECT department_id,
COUNT(*) AS people,
ROUND(AVG(salary)) AS avg_salary,
MAX(salary) AS top_salary
FROM employees
GROUP BY department_id
ORDER BY department_id;+---------------+--------+------------+------------+
| department_id | people | avg_salary | top_salary |
+---------------+--------+------------+------------+
| NULL | 1 | 95000.0 | 95000 |
| 1 | 6 | 108333.0 | 180000 |
| 2 | 5 | 86400.0 | 150000 |
| 3 | 3 | 80667.0 | 110000 |
| 4 | 2 | 92500.0 | 115000 |
| 5 | 3 | 65667.0 | 85000 |
+---------------+--------+------------+------------+The department numbers are not friendly. Joining to the departments table to show names is the subject of a later section, so for now we work with the ids.
The rule about the select list
Here is the rule that causes most GROUP BY errors. Each result row stands for a whole group, so every column you select must be one of two things: a column listed in GROUP BY, or something computed by an aggregate function. A plain column like first_name is a problem, because one group contains many different names and the result row has room for only one.
SELECT department_id, first_name, COUNT(*)
FROM employees
GROUP BY department_id;PostgreSQL and SQL Server reject this query with an error. MySQL 5.7 and later reject it too, in its default settings. SQLite accepts it and returns an arbitrary name from each group, which looks like it works and gives meaningless results. Treat the stricter behaviour as correct and never rely on SQLite's leniency.
The fix is to decide what you really want: add first_name to the GROUP BY (which changes the groups), or use an aggregate such as MIN(first_name), or drop the column.
Grouping by more than one column
List several columns and every distinct combination becomes a group. This counts people for each department and job title together:
SELECT department_id,
job_title,
COUNT(*) AS people
FROM employees
WHERE department_id IN (1, 2)
GROUP BY department_id, job_title
ORDER BY department_id, people DESC;+---------------+----------------------+--------+
| department_id | job_title | people |
+---------------+----------------------+--------+
| 1 | Senior Developer | 2 |
| 1 | Developer | 2 |
| 1 | Junior Developer | 1 |
| 1 | Engineering Director | 1 |
| 2 | Sales Associate | 2 |
| 2 | Account Executive | 2 |
| 2 | Sales Director | 1 |
+---------------+----------------------+--------+The order of columns in GROUP BY does not change the groups, only the readability. Notice that ORDER BY can use the alias people, because sorting happens after the select list is computed.
WHERE comes before grouping
WHERE filters individual rows first, then the survivors are grouped. This query counts only people paid at least 60000:
SELECT department_id,
COUNT(*) AS people
FROM employees
WHERE salary >= 60000
GROUP BY department_id
ORDER BY department_id;+---------------+--------+
| department_id | people |
+---------------+--------+
| NULL | 1 |
| 1 | 6 |
| 2 | 3 |
| 3 | 3 |
| 4 | 2 |
| 5 | 1 |
+---------------+--------+Compare with the earlier counts: Support dropped from 3 to 1 because two Support Engineers earn 56000. A condition on the result of an aggregate, such as "departments with more than three people", cannot go in WHERE. That is the job of HAVING, in the next lesson.
Grouping by an expression
You can group by a calculation, not just by a column. A classic example is counting rows per year using a function from the lesson on date functions:
SELECT strftime('%Y', hire_date) AS year,
COUNT(*) AS hires
FROM employees
GROUP BY strftime('%Y', hire_date)
ORDER BY year;+------+-------+
| year | hires |
+------+-------+
| 2016 | 1 |
| 2017 | 1 |
| 2018 | 2 |
| 2019 | 3 |
| 2020 | 3 |
| 2021 | 2 |
| 2022 | 3 |
| 2023 | 2 |
| 2024 | 3 |
+------+-------+The expression in SELECT and the one in GROUP BY should match exactly. SQLite, MySQL and PostgreSQL accept the alias, so GROUP BY year is also possible there, but SQL Server requires the full expression, which is why the portable form repeats it. The year-extraction function itself is database-specific, as you saw in that lesson.
Grouping by other tables' columns
Any table can be grouped. The departments table has a city column, and two departments sit in each of Mumbai and Pune:
SELECT city,
COUNT(*) AS departments
FROM departments
GROUP BY city
ORDER BY departments DESC, city;+-----------+-------------+
| city | departments |
+-----------+-------------+
| Mumbai | 2 |
| Pune | 2 |
| Bengaluru | 1 |
| Delhi | 1 |
+-----------+-------------+Here is one with real value, total hours booked for each project:
SELECT project_id,
COUNT(*) AS people,
SUM(hours) AS total_hours
FROM assignments
GROUP BY project_id
ORDER BY project_id;+------------+--------+-------------+
| project_id | people | total_hours |
+------------+--------+-------------+
| 1 | 4 | 1160 |
| 2 | 4 | 1100 |
| 3 | 3 | 480 |
| 4 | 5 | 1050 |
| 5 | 3 | 390 |
+------------+--------+-------------+NULL is one group
Missing values are not ignored when grouping. All rows with NULL in the grouping column fall into one group. In the manager example below, six employees have no manager, and they appear together as a NULL row:
SELECT manager_id,
COUNT(*) AS direct_reports
FROM employees
GROUP BY manager_id
ORDER BY manager_id;+------------+----------------+
| manager_id | direct_reports |
+------------+----------------+
| NULL | 6 |
| 1 | 2 |
| 2 | 2 |
| 3 | 1 |
| 6 | 2 |
| 7 | 2 |
| 10 | 2 |
| 13 | 1 |
| 15 | 2 |
+------------+----------------+Where NULL appears in a sorted result differs between databases: SQLite puts it first when sorting ascending, while PostgreSQL puts it last. If the position matters, handle it explicitly, as the lesson on ORDER BY explains.
Top groups with ORDER BY and LIMIT
Combine GROUP BY with ORDER BY on an aggregate and LIMIT to get a top-N list. Which job titles cost the most in total?
SELECT job_title,
COUNT(*) AS people,
SUM(salary) AS payroll
FROM employees
GROUP BY job_title
ORDER BY payroll DESC
LIMIT 5;+----------------------+--------+---------+
| job_title | people | payroll |
+----------------------+--------+---------+
| Senior Developer | 2 | 250000 |
| Engineering Director | 1 | 180000 |
| Account Executive | 2 | 180000 |
| Developer | 2 | 160000 |
| Sales Director | 1 | 150000 |
+----------------------+--------+---------+LIMIT is how SQLite, MySQL and PostgreSQL cut a result. SQL Server uses TOP 5 or OFFSET ... FETCH, and the standard form is FETCH FIRST 5 ROWS ONLY. The lesson on LIMIT and paging has the details.
Differences between databases
- Non-grouped columns. Covered above. SQLite allows them, the others reject them.
- Alias in GROUP BY. Allowed in SQLite, MySQL and PostgreSQL, not in SQL Server.
- Sorting of groups. Some databases used to return groups sorted by the grouping column as a side effect, but you must never rely on it. Always add
ORDER BYif the order matters. - MySQL extras. MySQL has
WITH ROLLUPto add subtotal rows, covered in HAVING and WITH ROLLUP in MySQL.
Common mistakes
- Selecting a column that is not grouped or aggregated. The most common GROUP BY error.
- Putting a condition on an aggregate in
WHERE. UseHAVING. - Forgetting the NULL group. Add
WHERE column IS NOT NULLif you do not want it. - Grouping by too much. Adding extra columns makes the groups smaller, until every group has one row and the summary is useless.
- Assuming groups come out in order. Sort with
ORDER BY.
Exercise
Show, for each project, the number of people assigned, the total hours, and the average hours per person (rounded to one decimal), but only for assignments of more than 100 hours. Sort by total hours, highest first. Then write a second query that finds how many employees each manager has, ignoring employees with no manager.
Show one possible answer
SELECT project_id,
COUNT(*) AS people,
SUM(hours) AS total_hours,
ROUND(AVG(hours), 1) AS avg_hours
FROM assignments
WHERE hours > 100
GROUP BY project_id
ORDER BY total_hours DESC;+------------+--------+-------------+-----------+
| project_id | people | total_hours | avg_hours |
+------------+--------+-------------+-----------+
| 1 | 4 | 1160 | 290.0 |
| 2 | 4 | 1100 | 275.0 |
| 4 | 4 | 990 | 247.5 |
| 3 | 2 | 400 | 200.0 |
| 5 | 1 | 260 | 260.0 |
+------------+--------+-------------+-----------+SELECT manager_id,
COUNT(*) AS direct_reports
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
ORDER BY direct_reports DESC, manager_id;+------------+----------------+
| manager_id | direct_reports |
+------------+----------------+
| 1 | 2 |
| 2 | 2 |
| 6 | 2 |
| 7 | 2 |
| 10 | 2 |
| 15 | 2 |
| 3 | 1 |
| 13 | 1 |
+------------+----------------+Key points
- GROUP BY splits the rows into groups that share the same value, and each aggregate function is then calculated once per group.
- Every column in the SELECT list must either be in GROUP BY or be inside an aggregate function. SQLite lets you break this rule, but PostgreSQL, SQL Server and modern MySQL do not.
- WHERE filters rows before they are grouped. To filter after grouping, use HAVING, which the next lesson covers.
- All rows with a NULL in the grouping column form one group of their own.