How WITH ROLLUP Works in MySQL

MySQL provides us with a handy WITH ROLLUP modifier that allows us to get totals and subtotals when using the GROUP BY clause.

Below is an example of how the MySQL WITH ROLLUP modifier works.

Example

Suppose we have a table called City and we run a simple SELECT query to retrieve some of its data:

SELECT
    CountryCode,
    District,
    Name,
    Population
FROM City
WHERE CountryCode IN ('AUS', 'NZL');

Result:

+-------------+-----------------+---------------+------------+
| CountryCode | District        | Name          | Population |
+-------------+-----------------+---------------+------------+
| AUS         | New South Wales | Sydney        |    3276207 |
| AUS         | Victoria        | Melbourne     |    2865329 |
| AUS         | Queensland      | Brisbane      |    1291117 |
| AUS         | West Australia  | Perth         |    1096829 |
| AUS         | South Australia | Adelaide      |     978100 |
| AUS         | Capital Region  | Canberra      |     322723 |
| AUS         | Queensland      | Gold Coast    |     311932 |
| AUS         | New South Wales | Newcastle     |     270324 |
| AUS         | New South Wales | Central Coast |     227657 |
| AUS         | New South Wales | Wollongong    |     219761 |
| AUS         | Tasmania        | Hobart        |     126118 |
| AUS         | Victoria        | Geelong       |     125382 |
| AUS         | Queensland      | Townsville    |     109914 |
| AUS         | Queensland      | Cairns        |      92273 |
| NZL         | Auckland        | Auckland      |     381800 |
| NZL         | Canterbury      | Christchurch  |     324200 |
| NZL         | Auckland        | Manukau       |     281800 |
| NZL         | Auckland        | North Shore   |     187700 |
| NZL         | Auckland        | Waitakere     |     170600 |
| NZL         | Wellington      | Wellington    |     166700 |
| NZL         | Dunedin         | Dunedin       |     119600 |
| NZL         | Hamilton        | Hamilton      |     117100 |
| NZL         | Wellington      | Lower Hutt    |      98100 |
+-------------+-----------------+---------------+------------+

Let’s say we want to group these results by the CountryCode, District, and then Name columns, and then provide a sum of the population of each country, district, and city within those results.

First, here’s a query without the WITH ROLLUP modifier:

SELECT
    CountryCode,
    District,
    Name,
    SUM(Population) AS Population
FROM City
WHERE CountryCode IN ('AUS', 'NZL')
GROUP BY CountryCode, District, Name;

Result:

+-------------+-----------------+---------------+------------+
| CountryCode | District        | Name          | Population |
+-------------+-----------------+---------------+------------+
| AUS         | New South Wales | Sydney        |    3276207 |
| AUS         | Victoria        | Melbourne     |    2865329 |
| AUS         | Queensland      | Brisbane      |    1291117 |
| AUS         | West Australia  | Perth         |    1096829 |
| AUS         | South Australia | Adelaide      |     978100 |
| AUS         | Capital Region  | Canberra      |     322723 |
| AUS         | Queensland      | Gold Coast    |     311932 |
| AUS         | New South Wales | Newcastle     |     270324 |
| AUS         | New South Wales | Central Coast |     227657 |
| AUS         | New South Wales | Wollongong    |     219761 |
| AUS         | Tasmania        | Hobart        |     126118 |
| AUS         | Victoria        | Geelong       |     125382 |
| AUS         | Queensland      | Townsville    |     109914 |
| AUS         | Queensland      | Cairns        |      92273 |
| NZL         | Auckland        | Auckland      |     381800 |
| NZL         | Canterbury      | Christchurch  |     324200 |
| NZL         | Auckland        | Manukau       |     281800 |
| NZL         | Auckland        | North Shore   |     187700 |
| NZL         | Auckland        | Waitakere     |     170600 |
| NZL         | Wellington      | Wellington    |     166700 |
| NZL         | Dunedin         | Dunedin       |     119600 |
| NZL         | Hamilton        | Hamilton      |     117100 |
| NZL         | Wellington      | Lower Hutt    |      98100 |
+-------------+-----------------+---------------+------------+

As expected it simply returned the population of each city in the results, but not for the countries or districts.

Now let’s add the WITH ROLLUP modifier to get the population of the countries and districts:

SELECT
    CountryCode,
    District,
    Name,
    SUM(Population) AS Population
FROM City
WHERE CountryCode IN ('AUS', 'NZL')
GROUP BY CountryCode, District, Name WITH ROLLUP;

Result:

+-------------+-----------------+---------------+------------+
| CountryCode | District        | Name          | Population |
+-------------+-----------------+---------------+------------+
| AUS         | Capital Region  | Canberra      |     322723 |
| AUS         | Capital Region  | NULL          |     322723 |
| AUS         | New South Wales | Central Coast |     227657 |
| AUS         | New South Wales | Newcastle     |     270324 |
| AUS         | New South Wales | Sydney        |    3276207 |
| AUS         | New South Wales | Wollongong    |     219761 |
| AUS         | New South Wales | NULL          |    3993949 |
| AUS         | Queensland      | Brisbane      |    1291117 |
| AUS         | Queensland      | Cairns        |      92273 |
| AUS         | Queensland      | Gold Coast    |     311932 |
| AUS         | Queensland      | Townsville    |     109914 |
| AUS         | Queensland      | NULL          |    1805236 |
| AUS         | South Australia | Adelaide      |     978100 |
| AUS         | South Australia | NULL          |     978100 |
| AUS         | Tasmania        | Hobart        |     126118 |
| AUS         | Tasmania        | NULL          |     126118 |
| AUS         | Victoria        | Geelong       |     125382 |
| AUS         | Victoria        | Melbourne     |    2865329 |
| AUS         | Victoria        | NULL          |    2990711 |
| AUS         | West Australia  | Perth         |    1096829 |
| AUS         | West Australia  | NULL          |    1096829 |
| AUS         | NULL            | NULL          |   11313666 |
| NZL         | Auckland        | Auckland      |     381800 |
| NZL         | Auckland        | Manukau       |     281800 |
| NZL         | Auckland        | North Shore   |     187700 |
| NZL         | Auckland        | Waitakere     |     170600 |
| NZL         | Auckland        | NULL          |    1021900 |
| NZL         | Canterbury      | Christchurch  |     324200 |
| NZL         | Canterbury      | NULL          |     324200 |
| NZL         | Dunedin         | Dunedin       |     119600 |
| NZL         | Dunedin         | NULL          |     119600 |
| NZL         | Hamilton        | Hamilton      |     117100 |
| NZL         | Hamilton        | NULL          |     117100 |
| NZL         | Wellington      | Lower Hutt    |      98100 |
| NZL         | Wellington      | Wellington    |     166700 |
| NZL         | Wellington      | NULL          |     264800 |
| NZL         | NULL            | NULL          |    1847600 |
| NULL        | NULL            | NULL          |   13161266 |
+-------------+-----------------+---------------+------------+

We can see that by adding WITH ROLLUP to our query, MySQL has calculated the totals for each city, district, for each country, and for the whole result set.

I should point out that this is a sample database, and it’s no way reflective of the actual population in those countries.

Change the `NULL` Headings

One problem with the default results of WITH ROLLUP is that it presents NULL as its label/heading. This could potentially make it difficult for us to quickly scan and understand the results. This would be even more pronounced if the underlying data also contained NULL values.

Fortunately, we can use the following technique to overcome this issue:

SELECT
    IF(GROUPING(CountryCode), 'All Countries', CountryCode) AS CountryCode,
    IF(GROUPING(District), 'All Districts', District) AS District,
    IF(GROUPING(Name), 'All Cities', Name) As CityName,
    SUM(Population) AS Population
FROM City
WHERE CountryCode IN ('AUS', 'NZL')
GROUP BY CountryCode, District, Name WITH ROLLUP;

Result:

+---------------+-----------------+---------------+------------+
| CountryCode   | District        | CityName      | Population |
+---------------+-----------------+---------------+------------+
| AUS           | Capital Region  | Canberra      |     322723 |
| AUS           | Capital Region  | All Cities    |     322723 |
| AUS           | New South Wales | Central Coast |     227657 |
| AUS           | New South Wales | Newcastle     |     270324 |
| AUS           | New South Wales | Sydney        |    3276207 |
| AUS           | New South Wales | Wollongong    |     219761 |
| AUS           | New South Wales | All Cities    |    3993949 |
| AUS           | Queensland      | Brisbane      |    1291117 |
| AUS           | Queensland      | Cairns        |      92273 |
| AUS           | Queensland      | Gold Coast    |     311932 |
| AUS           | Queensland      | Townsville    |     109914 |
| AUS           | Queensland      | All Cities    |    1805236 |
| AUS           | South Australia | Adelaide      |     978100 |
| AUS           | South Australia | All Cities    |     978100 |
| AUS           | Tasmania        | Hobart        |     126118 |
| AUS           | Tasmania        | All Cities    |     126118 |
| AUS           | Victoria        | Geelong       |     125382 |
| AUS           | Victoria        | Melbourne     |    2865329 |
| AUS           | Victoria        | All Cities    |    2990711 |
| AUS           | West Australia  | Perth         |    1096829 |
| AUS           | West Australia  | All Cities    |    1096829 |
| AUS           | All Districts   | All Cities    |   11313666 |
| NZL           | Auckland        | Auckland      |     381800 |
| NZL           | Auckland        | Manukau       |     281800 |
| NZL           | Auckland        | North Shore   |     187700 |
| NZL           | Auckland        | Waitakere     |     170600 |
| NZL           | Auckland        | All Cities    |    1021900 |
| NZL           | Canterbury      | Christchurch  |     324200 |
| NZL           | Canterbury      | All Cities    |     324200 |
| NZL           | Dunedin         | Dunedin       |     119600 |
| NZL           | Dunedin         | All Cities    |     119600 |
| NZL           | Hamilton        | Hamilton      |     117100 |
| NZL           | Hamilton        | All Cities    |     117100 |
| NZL           | Wellington      | Lower Hutt    |      98100 |
| NZL           | Wellington      | Wellington    |     166700 |
| NZL           | Wellington      | All Cities    |     264800 |
| NZL           | All Districts   | All Cities    |    1847600 |
| All Countries | All Districts   | All Cities    |   13161266 |
+---------------+-----------------+---------------+------------+

Here, we used MySQL’s GROUPING() function along with the IF() function to present user-friendly text as the headings for the rows that are generated by the WITH ROLLUP modifier. We presented All Countries, All Districts, or All Cities, depending on which column we were dealing with. This makes the result set more meaningful.

The GROUPING() function returns a 1 or 0, depending on whether or not the NULL value is due to the row representing a super-aggregate value. Specifically, it returns 1 if the expression value in the current row is a NULL representing a super-aggregate value. Otherwise, it returns 0, which indicates that the expression value is a NULL for a regular result row or is not NULL.

More Information

See the MySQL documentation for more information about the WITH ROLLUP modifier.

Example

Change the NULL Headings

More Information

Change the `NULL` Headings