MySQL provides us with the WITH ROLLUP modifier for the GROUP BY function for generating super aggregate data.
But by default, this modifier doesn’t label its data. By this I mean, it doesn’t provide us with meaningful labels for the rows that it generates. It simply uses NULL in those rows.
Maybe you’ve encountered this and are now looking for a solution.
Below is a handy little technique we can use to replace NULL with our own headings for these rows.
The Problem
First, let’s take a look at the problem that we’re trying to address.
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 | +-------------+-----------------+---------------+------------+
Here, we used WITH ROLLUP to generate super aggregate rows for the Population column. We can see that MySQL doesn’t generate labels/headings for those rows. Instead, it returns NULL.
The problem with this is that it makes it harder for us to understand what the super aggregate values in the Population column are for. This is especially true when we’re dealing with multiple levels of super aggregate data (such as subtotals, totals and grand totals, like we’re doing here).
It would be better if these rows had labels that helped us identify what the data represents.
The Solution
Here’s an example of how we can overcome the above problem:
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 now easily identify the super aggregate rows because of the label we gave them. In this case, we gave them labels of 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.
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.
The IF() function allows us to check for the value output by the GROUPING() function, and provide text if the GROUPING() function returns 1.