Keywords > WITH ROLLUP
Categories
Definition
The WITH ROLLUP clause is used with GROUP BY queries to generate extra rows in the dataset that represent group totals. Summary rows will show NULL in the group column(s) being summarized. WITH ROLLUP will summarize multiple level of groupings, providing totals across each group level. The WITH ROLLUP clause was added in MySQL 4.1.1.
The WITH ROLLUP clause cannot be used in a query with an ORDER BY clause. Summary columns can be manipulated in the query, allowing for functions like IFNULL() to be used to rename values in summary rows.
Sample
In this example, a query is performed to count customers in each city of each country, with summaries on a per-country and global basis. The per-country summary is identified by the presence of a NULL value in the city field, while the global summary is identified by the presence of a NULL value in both the city and country fields.
mysql> SELECT country.country, city.city, COUNT(address_id) -> FROM country LEFT JOIN city USING (country_id) -> LEFT JOIN address USING (city_id) -> GROUP by country, city -> WITH ROLLUP; | Yugoslavia | Kragujevac | 1 | | Yugoslavia | Novi Sad | 1 | | Yugoslavia | NULL | 2 | | Zambia | Kitwe | 1 | | Zambia | NULL | 1 | | NULL | NULL | 603 | +---------------------------------------+----------------------------+--------+ 710 rows in set (0.00 sec)
Further Reference
GROUP BY Modifiers (MySQL Reference Manual)