GROUP BY

GROUP BY collapses rows into one row per distinct key, and ONLY_FULL_GROUP_BY decides what else the select list may contain.

SyntaxSELECT ... FROM ... [WHERE ...] GROUP BY {col_name | expr} [, ...] [WITH ROLLUP]

GROUP BY collapses the rows that share a key into a single output row, so aggregate functions have something to aggregate over. One row comes out per distinct combination of the grouping expressions; all NULLs in a key column fall into one group together.

What may appear in the select list

MySQL 8.4 enables the ONLY_FULL_GROUP_BY SQL mode by default. Under it, the select list, HAVING and ORDER BY may only reference columns named in GROUP BY, columns functionally dependent on them, or expressions wrapped in an aggregate. Anything else is error 1055. MySQL does detect functional dependency: group by a primary key and you may select the rest of that table's columns, because the key determines them. When you know a column is single-valued per group but MySQL cannot prove it, ANY_VALUE(col) suppresses the check.

Grouping by an expression

As a MySQL extension you may group by a non-column expression, and you may use the select-list alias for it instead of repeating it. These two are the same statement:

GROUP BY id, FLOOR(value / 100)
GROUP BY id, val              -- where the select list says FLOOR(value/100) AS val

The alias form is shorthand; the optimizer resolves it to the same expression.

The 8.4 change to watch

Through MySQL 8.3, GROUP BY sorted its output implicitly under some conditions. In 8.4 it no longer does — ORDER BY NULL is unnecessary, and any query that relied on the free ordering must now add an explicit ORDER BY.

Example

Example · sql
-- Daily signup funnel, grouped by an expression and ordered explicitly.
SELECT
    DATE(created_at)                    AS signup_day,   -- grouping expression
    plan_tier,
    COUNT(*)                            AS signups,
    COUNT(verified_at)                  AS verified,     -- non-NULL only
    ROUND(AVG(seats), 1)                AS avg_seats
FROM accounts
WHERE created_at >= '2026-01-01'
GROUP BY signup_day, plan_tier          -- alias for DATE(created_at); same as repeating it
ORDER BY signup_day, plan_tier;         -- required in 8.4: GROUP BY no longer sorts

When to use it

  • Daily or monthly rollups of events by date expression.
  • Per-customer order totals keyed on the customer primary key.
  • Bucketing continuous values into a histogram.
  • Adding subtotal and grand-total rows to a sales report with WITH ROLLUP.

More examples

ONLY_FULL_GROUP_BY: the rejection and the three fixes

The error most people meet first after moving off MySQL 5.6 defaults. Grouping by the primary key is usually the right fix because it is both correct and unambiguous; keep ANY_VALUE for cases where you genuinely do not care which value of the group you get.

Example · sql
-- Rejected in MySQL 8.4 (ERROR 1055): city is neither grouped nor aggregated.
SELECT c.company, c.city, MAX(o.placed_at)
FROM customers AS c JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.company;

-- Fix 1: put it in the key.
SELECT c.company, c.city, MAX(o.placed_at)
FROM customers AS c JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.company, c.city;

-- Fix 2: group by the primary key. MySQL proves company and city depend on it.
SELECT c.id, c.company, c.city, MAX(o.placed_at)
FROM customers AS c JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id;

-- Fix 3: you know it is constant per group, MySQL cannot prove it.
SELECT c.company, ANY_VALUE(c.city), MAX(o.placed_at)
FROM customers AS c JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.company;

WITH ROLLUP for a subtotal row

ROLLUP adds super-aggregate rows: one per region, then one grand total. The catch is that it marks those rows with NULL in the grouping column, indistinguishable from a real NULL until you use GROUPING(). Use it for reports that need subtotals without a second query or a UNION.

Example · sql
SELECT
    COALESCE(region, 'ALL REGIONS')      AS region,   -- ROLLUP rows carry NULL in the key
    COALESCE(channel, 'all channels')    AS channel,
    SUM(amount_cents)                    AS revenue_cents
FROM orders
WHERE placed_at >= '2026-01-01'
GROUP BY region, channel WITH ROLLUP
ORDER BY GROUPING(region), region, GROUPING(channel), channel;
-- GROUPING(expr) returns 1 when the NULL is a rollup super-aggregate, 0 when it is data.

Grouping into buckets

Grouping by a computed expression rather than a stored column, which standard SQL does not allow but MySQL does. Note the key is the raw FLOOR() expression while the select list formats it — group by the thing that identifies the bucket, display whatever reads best.

Example · sql
-- Histogram of order values in 50-unit buckets.
SELECT
    FLOOR(amount_cents / 5000) * 50      AS bucket_floor_units,
    COUNT(*)                             AS orders,
    MIN(amount_cents)                    AS smallest,
    MAX(amount_cents)                    AS largest
FROM orders
WHERE status = 'paid'
GROUP BY FLOOR(amount_cents / 5000)      -- group by the expression, not the label
ORDER BY bucket_floor_units;

Related

sql havingsql countsql distinctsql order bysql window aggregates

Discussion

  • Be the first to comment on this lesson.