Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there a way to simulate GROUP BY WITH CUBE in MySql?

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?

like image 758
Charles Chen Avatar asked Aug 16 '11 21:08

Charles Chen


1 Answers

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.

like image 123
Harald Avatar answered Sep 25 '22 09:09

Harald