Sometimes when we use the SQL GROUP BY clause to group queries, it would be nice to have totals and subtotals included in the query results.
For example, if our query returns cities and their populations, grouped by the city’s district and perhaps also grouped by country, it would be nice to include subtotals for each district (based on the aggregate populations of all cities in the district). And it might also be nice to have the total population of each country, based on the same aggregate data.
Another way of putting it is that we want to get a summary of the summary. Or an aggregate of the aggregate. This is sometimes referred to as “super aggregate”.
Fortunately, in MySQL we can use the WITH ROLLUP modifier of the GROUP BY clause to achieve exactly that.
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 | +-------------+-----------------+---------------+------------+
Note that this is a sample database, and it’s no way reflective of the actual population in those countries.
Anyway, we can use the the GROUP BY clause to group this data by district and country.
And we can use the WITH ROLLUP modifier to provide subtotals of each district and country, as well as a grand total of the whole result set.
Example:
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 | +---------------+-----------------+---------------+------------+
We can see that the query has calculated and returned a separate row for each subtotal, total and the grand total. In our case, we can easily identify these rows due to the label that we gave them (i.e. All Countries, All Districts, and All Cities). We provided these labels by using a combination of the MySQL’s IF() function and its GROUPING() function.
These labels are optional, and our query could have been a lot simpler if we weren’t worried about having such labels. If we didn’t add these labels, a NULL value would’ve appeared instead.
Like this:
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 | +-------------+-----------------+---------------+------------+
So we essentially get the same data, it’s just that we don’t have nice user-friendly labels for our subtotals, totals, and the grand total. Instead, we have a whole bunch of NULL values.
Either way, we still get the same output. The actual population values are the same in either case.
This demonstrates that we don’t necessarily need to include all the other code to include subtotals, totals, and grand totals in our query results if we don’t want to. We can still get our subtotals, totals, and grand total simply by adding WITH ROLLUP to our GROUP BY query.