MySQL, Software Development

Aggregate gathered number of units using MySQL With Rollup

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.

When we run the SQL command, we get the following sample output.

mysql_with_rollup

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

 

Got comments or suggestions? We disabled the comments on this site to fight off spammers, but you can still contact us via our Facebook page!.


You Might Also Like