ROLLUPQuery Techniques
Buy Me A Coffee

An extension of GROUP BY that generates subtotal and grand-total rows automatically. Produces a result set with multiple levels of aggregation in a single query.

Generates hierarchical subtotals from the specified columns, from the most granular to a grand total. GROUP BY ROLLUP(a, b) produces groupings for (a, b), (a), and (). Useful for analytical reports with drill-down totals.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 4.1 MySQL uses the non-standard WITH ROLLUP modifier on GROUP BY, available since 4.1. MySQL 8.0 also accepts the standard GROUP BY ROLLUP(...) syntax. Use GROUPING(col) (added in 8.0) to distinguish NULL subtotal rows from actual NULL data values. HAVING filtering applies after rollup rows are generated.
PostgreSQL ✓ Supported 9.5 Standard GROUP BY ROLLUP(...) syntax. GROUPING(col) function available to identify rollup-generated NULL rows. Can be combined with GROUPING SETS and CUBE in the same GROUP BY clause.
SQL Server ✓ Supported 2008 Standard GROUP BY ROLLUP(...) syntax supported since 2008. The older WITH ROLLUP modifier (SQL Server 2005 syntax) is also still accepted. GROUPING(col) and GROUPING_ID(...) available to detect rollup rows.
Oracle ✓ Supported 8i Standard GROUP BY ROLLUP(...) syntax, available since Oracle 8i. GROUPING(col) and GROUPING_ID() available. Can combine ROLLUP, CUBE, and GROUPING SETS in one GROUP BY clause.
SQLite ✗ Not Supported — ROLLUP is not supported. Workaround: use UNION ALL to manually combine the detailed rows, subtotals, and grand total.

Details

ROLLUP(a, b, c) generates n+1 grouping levels: (a,b,c), (a,b), (a), and (). The NULL values in subtotal rows represent 'all values for this column' — use GROUPING(col) to distinguish them from genuine NULL data. MySQL is the only engine that uses a non-standard WITH ROLLUP modifier syntax as its primary form (though it also accepts standard syntax in 8.0+). SQLite is the only major engine with no ROLLUP support at all.

Standard Syntax

SELECT region, country, SUM(sales) AS total_sales FROM orders GROUP BY ROLLUP(region, country); -- Produces: per-country rows, per-region subtotals, grand total

Version Support

MySQL: Since 4.1 PostgreSQL: Since 9.5 SQL Server: Since 2008 Oracle: Since 8i SQLite: Not supported

Per-Database Syntax & Notes

MySQL

MySQL uses the non-standard WITH ROLLUP modifier on GROUP BY, available since 4.1. MySQL 8.0 also accepts the standard GROUP BY ROLLUP(...) syntax. Use GROUPING(col) (added in 8.0) to distinguish NULL subtotal rows from actual NULL data values. HAVING filtering applies after rollup rows are generated.

-- Non-standard (works since 4.1): SELECT region, country, SUM(sales) AS total_sales FROM orders GROUP BY region, country WITH ROLLUP; -- Standard syntax (8.0+): SELECT region, country, SUM(sales) AS total_sales FROM orders GROUP BY ROLLUP(region, country); -- Distinguish subtotal NULLs from real NULLs (8.0+): SELECT IF(GROUPING(region), 'All Regions', region) AS region, IF(GROUPING(country), 'Subtotal', country) AS country, SUM(sales) AS total_sales FROM orders GROUP BY region, country WITH ROLLUP;

PostgreSQL

Standard GROUP BY ROLLUP(...) syntax. GROUPING(col) function available to identify rollup-generated NULL rows. Can be combined with GROUPING SETS and CUBE in the same GROUP BY clause.

SELECT region, country, SUM(sales) AS total_sales FROM orders GROUP BY ROLLUP(region, country); -- Identify rollup rows: SELECT CASE GROUPING(region) WHEN 1 THEN 'All Regions' ELSE region END AS region, CASE GROUPING(country) WHEN 1 THEN 'Subtotal' ELSE country END AS country, SUM(sales) AS total_sales FROM orders GROUP BY ROLLUP(region, country);

SQL Server

Standard GROUP BY ROLLUP(...) syntax supported since 2008. The older WITH ROLLUP modifier (SQL Server 2005 syntax) is also still accepted. GROUPING(col) and GROUPING_ID(...) available to detect rollup rows.

-- Standard syntax: SELECT region, country, SUM(sales) AS total_sales FROM orders GROUP BY ROLLUP(region, country); -- Legacy syntax (still works): SELECT region, country, SUM(sales) AS total_sales FROM orders GROUP BY region, country WITH ROLLUP; -- GROUPING_ID for multi-level identification: SELECT region, country, SUM(sales) AS total_sales, GROUPING_ID(region, country) AS grouping_level FROM orders GROUP BY ROLLUP(region, country);

Oracle

Standard GROUP BY ROLLUP(...) syntax, available since Oracle 8i. GROUPING(col) and GROUPING_ID() available. Can combine ROLLUP, CUBE, and GROUPING SETS in one GROUP BY clause.

SELECT region, country, SUM(sales) AS total_sales FROM orders GROUP BY ROLLUP(region, country); -- With GROUPING to label subtotals: SELECT DECODE(GROUPING(region), 1, 'All Regions', region) AS region, DECODE(GROUPING(country), 1, 'Subtotal', country) AS country, SUM(sales) AS total_sales FROM orders GROUP BY ROLLUP(region, country);

SQLite

ROLLUP is not supported. Workaround: use UNION ALL to manually combine the detailed rows, subtotals, and grand total.

-- Workaround with UNION ALL: SELECT region, country, SUM(sales) AS total_sales FROM orders GROUP BY region, country UNION ALL SELECT region, NULL, SUM(sales) FROM orders GROUP BY region UNION ALL SELECT NULL, NULL, SUM(sales) FROM orders;