Add Subtotals and a Grand Total With ROLLUP

Use `GROUP BY ROLLUP` to produce subtotals and a grand total alongside regular grouped rows in one query.

4/4 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Show total sales per region plus a grand total row

East contributed 300 + 200 = 500 and West contributed 150 + 250 = 400. ROLLUP adds a third row where region is NULL (the rollup placeholder) and total is 900 — the sum of all rows. GROUPING(region) returns 0 for the East and West rows and 1 for the rollup row, which the ORDER BY uses to push the grand total to the end.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE sales (region VARCHAR(20), amount INT);

INSERT INTO
  sales
VALUES
  ('East', 300),
  ('East', 200),
  ('West', 150),
  ('West', 250);

Validated query

SQL

SELECT
  region,
  SUM(amount) AS total
FROM
  sales
GROUP BY
  region
WITH
  ROLLUP
ORDER BY
  GROUPING(region),
  region;

Expected result

region	total
East	500
West	400
NULL	900

SQL Server PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE sales (region VARCHAR(20), amount INT);

INSERT INTO
  sales
VALUES
  ('East', 300),
  ('East', 200),
  ('West', 150),
  ('West', 250);

Validated query

SQL

SELECT
  region,
  SUM(amount) AS total
FROM
  sales
GROUP BY
  ROLLUP (region)
ORDER BY
  GROUPING(region),
  region;

Expected result

region	total
East	500
West	400
NULL	900

MySQL and MariaDB use `GROUP BY col WITH ROLLUP`. PostgreSQL and SQL Server use `GROUP BY ROLLUP(col)`. The output is identical across supported engines. `ORDER BY GROUPING(region), region` places normal rows first (GROUPING = 0) and the grand-total row last (GROUPING = 1).

Useful when

Where this command helps.

producing a report with per-group totals and a grand total without running two separate queries
building financial summaries with region subtotals and a company-wide total in a single pass

Explanation

What the command is doing.

ROLLUP is a GROUP BY modifier that adds extra summary rows to the result. For a single grouping column it produces one regular row per group plus one grand-total row with NULL in the grouping column. For multiple columns it produces a hierarchy of subtotals. The GROUPING(col) function returns 1 for rollup-generated NULL values and 0 for real data, letting you distinguish a genuine NULL in the data from a rollup placeholder. MySQL and MariaDB use GROUP BY col WITH ROLLUP; PostgreSQL and SQL Server use the SQL-standard GROUP BY ROLLUP(col) syntax.

Add Subtotals and a Grand Total With ROLLUP

Show total sales per region plus a grand total row

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.