Aggregation and GROUP BY
Aggregate functions (COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT, JSON_ARRAYAGG, STD, VARIANCE, BIT_AND/OR/XOR) summarize rows.
Concept
Aggregate functions (COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT, JSON_ARRAYAGG, STD, VARIANCE, BIT_AND/OR/XOR) summarize rows. GROUP BY forms groups; HAVING filters groups. MySQL adds WITH ROLLUP, GROUP_CONCAT, and enforces grouping rules through the ONLY_FULL_GROUP_BY SQL mode.
Why It Matters
Reports and KPIs are aggregations. The ONLY_FULL_GROUP_BY rules are one of the most common MySQL errors for developers migrating from old MySQL versions.
Syntax
SELECT group_cols, AGG(expr)
FROM t
[WHERE row_filter]
GROUP BY group_cols [WITH ROLLUP]
[HAVING group_filter]
[ORDER BY ...];Example
11.1 Aggregates and NULL COUNT(*) counts rows; COUNT(col) skips NULLs; aggregates other than COUNT(*) ignore NULLs.
SUM/AVG/MIN/MAX over zero rows return NULL → use IFNULL(SUM(x), 0).
AVG of DECIMAL returns extra decimal places (117500.000000); wrap with ROUND.
11.2 GROUP BY and HAVING (NULL forms one group and sorts first in MySQL.)
WHERE filters rows before grouping; HAVING filters groups after.
Columns functionally dependent on the GROUP BY key are allowed: GROUP BY c.customer_id lets you select c.name because customer_id is the primary key of c.
Do not disable ONLY_FULL_GROUP_BY to "fix" the error; the old behavior returned arbitrary values.
GROUP_CONCAT output is truncated at group_concat_max_len (default 1024 bytes) with only a warning — increase it for long lists: SET SESSION group_concat_max_len = 100000;.
11.5 Conditional aggregation 11.6 WITH ROLLUP and GROUPING() (GROUPING() exists since 8.0.1. MySQL does not support CUBE or GROUPING SETS.) 11.7 JSON aggregation
SELECT COUNT(*) AS employees, COUNT(dept_id) AS with_dept, COUNT(DISTINCT dept_id) AS depts,
SUM(salary) AS payroll, AVG(salary) AS avg_salary, MIN(hire_date) AS first_hire, MAX(salary) AS top_salary
FROM employees;+-----------+-----------+-------+-----------+---------------+------------+------------+
| employees | with_dept | depts | payroll | avg_salary | first_hire | top_salary |
+-----------+-----------+-------+-----------+---------------+------------+------------+
| 8 | 7 | 3 | 940000.00 | 117500.000000 | 2015-03-01 | 250000.00 |
+-----------+-----------+-------+-----------+---------------+------------+------------+SELECT dept_id, COUNT(*) AS headcount, SUM(salary) AS total_salary, ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY dept_id
HAVING COUNT(*) >= 1
ORDER BY dept_id;+---------+-----------+--------------+------------+
| dept_id | headcount | total_salary | avg_salary |
+---------+-----------+--------------+------------+
| NULL | 1 | 80000.00 | 80000.00 |
| 1 | 4 | 590000.00 | 147500.00 |
| 2 | 2 | 180000.00 | 90000.00 |
| 3 | 1 | 90000.00 | 90000.00 |
+---------+-----------+--------------+------------+-- Customers with non-cancelled spend above 50000
SELECT customer_id, COUNT(*) AS n_orders, SUM(total_amount) AS spent
FROM orders
WHERE status <> 'CANCELLED'
GROUP BY customer_id
HAVING spent > 50000 -- MySQL allows SELECT aliases in HAVING
ORDER BY spent DESC;+-------------+----------+-----------+
| customer_id | n_orders | spent |
+-------------+----------+-----------+
| 2 | 2 | 105000.00 |
| 1 | 2 | 93900.00 |
+-------------+----------+-----------+SELECT dept_id, first_name, COUNT(*) FROM employees GROUP BY dept_id;
-- ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains
-- nonaggregated column 'eh_company.employees.first_name' which is not functionally dependent on
-- columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_bySELECT dept_id, MAX(first_name) AS any_name, COUNT(*) FROM employees GROUP BY dept_id; -- aggregate it
SELECT dept_id, ANY_VALUE(first_name) AS some_name, COUNT(*) FROM employees GROUP BY dept_id; -- explicitly "any value"
SELECT dept_id, GROUP_CONCAT(first_name ORDER BY first_name) AS names, COUNT(*) FROM employees GROUP BY dept_id;SELECT category,
GROUP_CONCAT(product_name ORDER BY product_name SEPARATOR ', ') AS products,
COUNT(*) AS n
FROM products
GROUP BY category
ORDER BY category;+-------------+---------------------------+---+
| category | products | n |
+-------------+---------------------------+---+
| Electronics | Laptop, Monitor, Mouse | 3 |
| Furniture | Desk Chair, Standing Desk | 2 |
| Stationery | Notebook | 1 |
+-------------+---------------------------+---+Expected Result
SELECT category, COUNT(*) AS n_products, ROUND(AVG(price), 2) AS avg_price, MAX(price) AS max_price
FROM products GROUP BY category ORDER BY category;+-------------+------------+-----------+-----------+
| category | n_products | avg_price | max_price |
+-------------+------------+-----------+-----------+
| Electronics | 3 | 31266.67 | 75000.00 |
| Furniture | 2 | 23500.00 | 35000.00 |
| Stationery | 1 | 50.00 | 50.00 |
+-------------+------------+-----------+-----------+Common Mistakes
Disabling ONLY_FULL_GROUP_BY instead of fixing queries.
Silent truncation of GROUP_CONCAT.
Aggregate conditions in WHERE.
Expecting GROUP BY to sort (MySQL 8 no longer sorts implicitly; GROUP BY ... ASC/DESC was removed).
COUNT(*) after LEFT JOIN when COUNT(child.id) was needed.
Best Practices
Keep ONLY_FULL_GROUP_BY on.
Use ANY_VALUE() only when any value is genuinely acceptable.
Always add ORDER BY to grouped results.
Use conditional aggregation for pivot-style reports.
Interview Relevance
Very high: "WHERE vs HAVING?", "What is ONLY_FULL_GROUP_BY?", "GROUP_CONCAT limits?", "What does WITH ROLLUP do?", "COUNT(*) vs COUNT(col)?".
Practice Questions
Count orders and revenue per status.
List job titles held by more than one employee.
For each customer, list their order IDs as a comma-separated string.
Produce monthly revenue with a grand-total row using WITH ROLLUP.
Fix a query that fails with error 1055 in three different ways.