Aggregate gathered number of units using MySQL’s With Rollup

MySQL has a number of non-ISO standard constructs that are neat, efficient, and somewhat unconventional given the fact it started out as a very simple 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.  One of these is the WITH ROLLUP clause used in conjunction with the SQL GROUP clause. It basically allows for aggregation of already gathered number of units like summing up subtotals.

For example,

This produces:


The number at the bottom represents the sum of all values above it.  Notice that with WITH ROLLUP, only one  SQL command is processed or sent out by a client.

* Using MySQL 5.5