This post shows how to aggregate population averages in MYSQL using Rollup. MySQL has several non-ISO standard constructs that are neat, efficient, and somewhat unconventional, given the fact it started as a straightforward database. These are not portable to other RDBMS and, therefore, might not be a good idea to go about things if you have plans to move to other platforms in the future.
Aggregate Using MySQL WITH ROLLUP
One of these is using MYSQL WITH ROLLUP clause used with the SQL GROUP clause. It allows for aggregating the already gathered number of units, like summing up subtotals. For example, consider the following SQL command. It looks like any SQL command with the GROUP BY clause. However, we use the MySQL WITH ROLLUP clause to aggregate all the average population. As a result, it creates an extra row with NULL content. But this row has the sum of all average populations.
1 2 | SELECT Continent, AVG(Population) AS avg_pop FROM Country GROUP BY Continent WITH ROLLUP; |
When we run the SQL command, we get the following sample output.
The number at the bottom represents the sum of all values above it. Notice that using WITH ROLLUP, only one SQL command is processed or sent out by a client. That is how we aggregate group results using MySQL WITH ROLLUP clause.
Want to test this feature quickly? Try MySQL with Docker compose.
* Using MySQL 5.5
