# SELECT WITH ROLLUP ## Syntax See [SELECT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select). ## Description The `WITH ROLLUP` modifier adds extra rows to the result set that represent super-aggregate summaries. The super-aggregated column is represented by a `NULL` value. Multiple aggregates over different columns will be added if there are multiple `GROUP BY` columns. The [LIMIT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/limit) clause can be used at the same time, and is applied after the `WITH ROLLUP` rows have been added. `WITH ROLLUP` cannot be used with [ORDER BY](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/order-by). Some sorting is still possible by using `ASC` or `DESC` clauses with the `GROUP BY` column, although the super-aggregate rows will always be added last. ## Examples These examples use the following sample table ```sql CREATE TABLE booksales ( country VARCHAR(35), genre ENUM('fiction','non-fiction'), year YEAR, sales INT); INSERT INTO booksales VALUES ('Senegal','fiction',2014,12234), ('Senegal','fiction',2015,15647), ('Senegal','non-fiction',2014,64980), ('Senegal','non-fiction',2015,78901), ('Paraguay','fiction',2014,87970), ('Paraguay','fiction',2015,76940), ('Paraguay','non-fiction',2014,8760), ('Paraguay','non-fiction',2015,9030); ``` The `WITH ROLLUP` modifier in this example adds an extra row that aggregates both years: ```sql SELECT year, SUM(sales) FROM booksales GROUP BY year; +------+------------+ | year | SUM(sales) | +------+------------+ | 2014 | 173944 | | 2015 | 180518 | +------+------------+ 2 rows in set (0.08 sec) SELECT year, SUM(sales) FROM booksales GROUP BY year WITH ROLLUP; +------+------------+ | year | SUM(sales) | +------+------------+ | 2014 | 173944 | | 2015 | 180518 | | NULL | 354462 | +------+------------+ ``` Each time the genre, the year, or the country change, another super-aggregate row is added: ```sql SELECT country, year, genre, SUM(sales) FROM booksales GROUP BY country, year, genre; +----------+------+-------------+------------+ | country | year | genre | SUM(sales) | +----------+------+-------------+------------+ | Paraguay | 2014 | fiction | 87970 | | Paraguay | 2014 | non-fiction | 8760 | | Paraguay | 2015 | fiction | 76940 | | Paraguay | 2015 | non-fiction | 9030 | | Senegal | 2014 | fiction | 12234 | | Senegal | 2014 | non-fiction | 64980 | | Senegal | 2015 | fiction | 15647 | | Senegal | 2015 | non-fiction | 78901 | +----------+------+-------------+------------+ SELECT country, year, genre, SUM(sales) FROM booksales GROUP BY country, year, genre WITH ROLLUP; +----------+------+-------------+------------+ | country | year | genre | SUM(sales) | +----------+------+-------------+------------+ | Paraguay | 2014 | fiction | 87970 | | Paraguay | 2014 | non-fiction | 8760 | | Paraguay | 2014 | NULL | 96730 | | Paraguay | 2015 | fiction | 76940 | | Paraguay | 2015 | non-fiction | 9030 | | Paraguay | 2015 | NULL | 85970 | | Paraguay | NULL | NULL | 182700 | | Senegal | 2014 | fiction | 12234 | | Senegal | 2014 | non-fiction | 64980 | | Senegal | 2014 | NULL | 77214 | | Senegal | 2015 | fiction | 15647 | | Senegal | 2015 | non-fiction | 78901 | | Senegal | 2015 | NULL | 94548 | | Senegal | NULL | NULL | 171762 | | NULL | NULL | NULL | 354462 | +----------+------+-------------+------------+ ``` The `LIMIT` clause, applied after `WITH ROLLUP`: ```sql SELECT country, year, genre, SUM(sales) FROM booksales GROUP BY country, year, genre WITH ROLLUP LIMIT 4; +----------+------+-------------+------------+ | country | year | genre | SUM(sales) | +----------+------+-------------+------------+ | Paraguay | 2014 | fiction | 87970 | | Paraguay | 2014 | non-fiction | 8760 | | Paraguay | 2014 | NULL | 96730 | | Paraguay | 2015 | fiction | 76940 | +----------+------+-------------+------------+ ``` Sorting by year descending: ```sql SELECT country, year, genre, SUM(sales) FROM booksales GROUP BY country, year DESC, genre WITH ROLLUP; +----------+------+-------------+------------+ | country | year | genre | SUM(sales) | +----------+------+-------------+------------+ | Paraguay | 2015 | fiction | 76940 | | Paraguay | 2015 | non-fiction | 9030 | | Paraguay | 2015 | NULL | 85970 | | Paraguay | 2014 | fiction | 87970 | | Paraguay | 2014 | non-fiction | 8760 | | Paraguay | 2014 | NULL | 96730 | | Paraguay | NULL | NULL | 182700 | | Senegal | 2015 | fiction | 15647 | | Senegal | 2015 | non-fiction | 78901 | | Senegal | 2015 | NULL | 94548 | | Senegal | 2014 | fiction | 12234 | | Senegal | 2014 | non-fiction | 64980 | | Senegal | 2014 | NULL | 77214 | | Senegal | NULL | NULL | 171762 | | NULL | NULL | NULL | 354462 | +----------+------+-------------+------------+ ``` ## See Also * [SELECT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select) * [Joins and Subqueries](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/set-operations) * [LIMIT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/limit) * [ORDER BY](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/order-by) * [GROUP BY](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/group-by) * [Common Table Expressions](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/common-table-expressions) * [SELECT INTO OUTFILE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select-into-outfile) * [SELECT INTO DUMPFILE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select-into-dumpfile) * [FOR UPDATE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/for-update) * [LOCK IN SHARE MODE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/lock-in-share-mode) * [Optimizer Hints](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/optimizer-hints) *This page is licensed: CC BY-SA / Gnu FDL* {% @marketo/form formId="4316" %}