GROUP BY, HAVING, and ROLLUP

GROUP BY collapses rows sharing its expressions into one row per group for the aggregates (Aggregates and Windows), and HAVING filters the groups. Under the default ONLY_FULL_GROUP_BY mode every select-list column must be grouped, aggregated, or functionally dependent on the grouped columns:

A column that is not grouped, then subtotals with ROLLUPSQL

SELECT customer_id, status, COUNT(*) FROM orders GROUP BY customer_id;
SELECT IF(GROUPING(category_id), 'all', category_id) AS category,
       COUNT(*) AS titles, SUM(stock) AS units
FROM products WHERE category_id IN (2, 3)
GROUP BY ROLLUP (category_id);

Output

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains
  nonaggregated column 'shop.orders.status' which is not functionally dependent on columns in
    GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
+----------+--------+-------+
| category | titles | units |
+----------+--------+-------+
| 2        |      3 |    52 |
| 3        |      2 |    12 |
| all      |      5 |    64 |
+----------+--------+-------+
3 rows in set (0.000 sec)

A customer's group has several statuses, and MySQL 524 refuses to pick one at random as versions before 5.7.5 did: group by it, aggregate it, or write ANY_VALUE(status). Grouping by a primary key makes the rest of the row dependent, so GROUP BY c.id may select c.name.

ROLLUP adds super-aggregate rows: a grand total here, plus per-a subtotals with ROLLUP (a, b). GROUPING() returns 1 on those rows, whose grouped column is NULL. The GROUP BY ROLLUP (...) spelling arrived in MySQL 9.1; WITH ROLLUP also runs on 8.4 LTS. And since 8.0, GROUP BY no longer sorts, so add ORDER BY.

MENU

GROUP BY, HAVING, and ROLLUP