GROUP BY splits rows into groups that share the same values, then aggregates each group into a single output row.

One row per group

SQL

SELECT dept_id,
       COUNT(*)            AS headcount,
       SUM(salary)         AS payroll,
       ROUND(AVG(salary))  AS avg_salary,
       MAX(salary)         AS top_salary
FROM employees
GROUP BY dept_id
ORDER BY dept_id;

Output

dept_id | headcount | payroll | avg_salary | top_salary
--------+-----------+---------+------------+-----------
NULL    | 1         | 60000   | 60000.0    | 60000
1       | 4         | 460000  | 115000.0   | 150000
2       | 3         | 252000  | 84000.0    | 110000
3       | 2         | 165000  | 82500.0    | 85000

Dev, with no department, forms his own NULL group — all NULLs are grouped together.

The golden rule

Every column in SELECT must either be in GROUP BY or be inside an aggregate. Otherwise the query asks for “the name” of a group that contains four names.

SQLite — allowed, but not portable

SELECT dept_id, name, MAX(salary) AS top_salary      -- name is not grouped or aggregated
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id
ORDER BY dept_id;

Output

dept_id | name  | top_salary
--------+-------+-----------
1       | Asha  | 150000
2       | Priya | 110000
3       | Sara  | 85000

Grouping by several columns or expressions

SQL

SELECT p.category,
       o.status,
       SUM(oi.quantity) AS units
FROM order_items oi
JOIN orders o   ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.category, o.status
ORDER BY p.category, o.status;

Output

category    | status    | units
------------+-----------+------
Electronics | cancelled | 2
Electronics | delivered | 3
Electronics | shipped   | 1
Furniture   | delivered | 2
Furniture   | shipped   | 3
Stationery  | delivered | 20
Stationery  | pending   | 20

SQL

SELECT strftime('%Y', hire_date) AS hire_year, COUNT(*) AS hires
FROM employees
WHERE hire_date >= '2020-01-01'
GROUP BY hire_year
ORDER BY hire_year;

Output

hire_year | hires
----------+------
2020      | 1
2021      | 1
2022      | 1
2023      | 1
2024      | 1

Subtotals: ROLLUP, CUBE, GROUPING SETS

PostgreSQL / SQL Server / Oracle / MySQL

SELECT dept_id, EXTRACT(YEAR FROM hire_date) AS yr, SUM(salary)
FROM employees
GROUP BY ROLLUP (dept_id, yr);          -- per (dept, year), per dept, grand total

-- MySQL: GROUP BY dept_id, yr WITH ROLLUP
-- GROUPING SETS ((dept_id), (yr), ())  -- exactly the groupings you list
-- CUBE (dept_id, yr)                   -- every combination

SQLite has no ROLLUP; the same result is a UNION ALL of the groupings:

SQL

SELECT d.name AS department, SUM(e.salary) AS payroll
FROM employees e JOIN departments d ON d.dept_id = e.dept_id
GROUP BY d.name
UNION ALL
SELECT 'ALL DEPARTMENTS', SUM(e.salary)
FROM employees e JOIN departments d ON d.dept_id = e.dept_id
ORDER BY payroll;

Output

department      | payroll
----------------+--------
HR              | 165000
Sales           | 252000
Engineering     | 460000
ALL DEPARTMENTS | 877000

Can you GROUP BY a column that is not in the SELECT list?

What does GROUP BY do with NULL values?