GROUP BY Fundamentals

Concept

GROUP BY divides the rows that survived WHERE into buckets - one per distinct value of the grouping columns - and returns exactly one row per bucket. Aggregate functions then summarise each bucket.

Syntax

SELECT   grouping_column, aggregate_function(other_column)
FROM     table_name
WHERE    row_condition
GROUP BY grouping_column
ORDER BY grouping_column;

Example

SELECT dept_id,
       COUNT(*)              AS headcount,
       ROUND(AVG(salary), 2) AS avg_salary,
       MAX(salary)           AS top_salary,
       SUM(salary)           AS payroll
FROM   employees
WHERE  status = 'active'
GROUP BY dept_id
ORDER BY payroll DESC;

Explanation

Read that query in evaluation order, not in written order:

1. FROM employees - start with all 8 rows.
2. WHERE status = 'active' - drop the inactive employee. 7 rows remain.
3. GROUP BY dept_id - form one bucket per department, plus one bucket for the NULL department.
4. Aggregates run per bucket.
5. ORDER BY sorts the resulting rows.

The one rule

Every column in the SELECT list must either appear in the GROUP BY clause or be wrapped in an aggregate function.

-- Invalid: which of the three names in department 10 should first_name return?
SELECT dept_id, first_name, COUNT(*)
FROM   employees
GROUP BY dept_id;

-- Valid: the name is aggregated
SELECT dept_id, MAX(first_name) AS a_name, COUNT(*) AS headcount
FROM   employees
GROUP BY dept_id;

-- Valid: the name is part of the group key
SELECT dept_id, first_name, COUNT(*) AS rows_per_person
FROM   employees
GROUP BY dept_id, first_name;

PostgreSQL, SQL Server and Oracle reject the first query outright. MySQL and MariaDB reject it too when ONLY_FULL_GROUP_BY is enabled - which is the default in MySQL 8 - and otherwise return an arbitrary value from the group, which is worse than an error because it looks like it worked.

Important rules

• All NULLs in a grouping column form one single group.
• WHERE runs before grouping, so it cannot reference an aggregate.
• The result has one row per distinct combination of grouping columns - no more, no fewer.
• Grouping by an expression is allowed; repeat the expression in GROUP BY (or group by its ordinal position, which is best avoided).
• GROUP BY does not guarantee sorted output. Add ORDER BY if order matters.

Common mistakes

• Selecting a column that is neither grouped nor aggregated, and trusting whatever MySQL returns.
• Expecting GROUP BY to sort the result. Some engines happen to; none promise to.
• Grouping by a column with a different granularity than intended - grouping by order_date when you wanted per month.
• Forgetting that groups only exist for values present in the data: a department with no employees produces no row at all. Use a LEFT JOIN from departments to see zeroes.

Best practices

• Enable ONLY_FULL_GROUP_BY on MySQL so the database catches ambiguous queries for you.
• List grouping columns in the same order in SELECT and GROUP BY; it reads better and matches index order.
• Always add an explicit ORDER BY for anything a human will read.
• To include empty groups, start from the dimension table and LEFT JOIN the facts.

Practice

1. Count orders and total revenue per customer.
2. Return the number of employees per department, including the group with no department.
3. Why does SELECT dept_id, first_name, COUNT(*) FROM employees GROUP BY dept_id fail, and what are two valid rewrites?