Aggregations & GROUP BY

Aggregate functions collapse many rows into one summary number. Combined with GROUP BY they answer most reporting questions: revenue per branch, headcount per department, average basket size per month.

Aggregate functions

Function	Returns
COUNT(*)	number of rows, including rows with NULLs
COUNT(col)	number of rows where col is not NULL
COUNT(DISTINCT col)	number of different values
SUM(col)	total
AVG(col)	mean, ignoring NULLs
MIN / MAX	smallest / largest
GROUP_CONCAT(col)	the values joined into one string (MySQL only)

SELECT COUNT(*)              AS employees,
       ROUND(AVG(salary), 0) AS avg_salary,
       MAX(salary)           AS top_salary
FROM employees;

Without GROUP BY, the whole table is one group and you get a single row back.

GROUP BY

SELECT department,
       COUNT(*)        AS headcount,
       AVG(salary)     AS avg_salary,
       MAX(salary)     AS top_salary
FROM employees
GROUP BY department
HAVING COUNT(*) >= 5
ORDER BY avg_salary DESC;

• WHERE filters rows before grouping.
• HAVING filters groups after aggregation.

So "average salary per department, counting only people who joined after April 2024, keeping departments with at least five such people" becomes:

SELECT department, COUNT(*) AS headcount, ROUND(AVG(salary)) AS avg_salary
FROM employees
WHERE joined_on >= '2024-04-01'
GROUP BY department
HAVING headcount >= 5;

Grouping by several columns

Every distinct combination becomes one row.

SELECT city, department, COUNT(*) AS headcount, SUM(salary) AS monthly_cost
FROM employees
GROUP BY city, department
ORDER BY city, monthly_cost DESC;

Group by an expression to build a time series:

SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
       COUNT(*)             AS orders,
       SUM(total)           AS revenue,
       ROUND(AVG(total), 2) AS avg_order_value
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY month
ORDER BY month;

Subtotals with ROLLUP

WITH ROLLUP adds a grand-total row — and subtotal rows for multi-column groupings — with NULL in the rolled-up columns.

SELECT city, SUM(total) AS revenue
FROM orders
GROUP BY city WITH ROLLUP;

ONLY_FULL_GROUP_BY

MySQL 8 enables ONLY_FULL_GROUP_BY by default: every selected column must appear in GROUP BY or sit inside an aggregate. This query fails with error 1055, because one department has many names:

SELECT department, name, AVG(salary)
FROM employees
GROUP BY department;

Fix it by aggregating the extra column (MAX(name), GROUP_CONCAT(name)) or by adding it to GROUP BY.

Common mistakes

• Putting an aggregate in WHERE (WHERE COUNT(*) > 5); aggregates belong in HAVING.
• Expecting COUNT(bonus) to equal COUNT(*) when bonus contains NULLs.
• Letting AVG skip NULLs silently — use AVG(COALESCE(bonus, 0)) if a missing bonus should count as zero.
• Omitting ORDER BY; MySQL 8 no longer sorts GROUP BY results for you.
• Averaging a column that is already a percentage instead of recomputing it from the underlying totals.