MySql supports GROUP BY WITH ROLLUP which will return aggregates for the last x of the n columns in the group by but does not support GROUP BY WITH CUBE to take all combinations of the n columns and take aggregates.
I can simulate this by doing unions of GROUP BY WITH ROLLUP queries, but MySql is materializing my subquery multiple times. I am using a group by on a large subquery, so this is suboptimal. Is there a way to solve this without temporary tables?
Short answer: No.
Long answer: You may install an open source data warehouse with GROUP BY WITH CUBE support which is using Mysql as a storage engine, such as Pentaho.
The use of Roll up is the solution to build the cube with the faces and corners that you visualize. In this case I assume you use virtual ROLAP. If you have enough memory. I propose to use the memory engine to materialize the cube, and make use of drill and slice operations or cube operations as needed.
