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

mysql group-by cube rollup
Charles Chen picture Charles Chen · Aug 16, 2011 · Viewed 10.5k times · Source

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?

Answer

Harald picture Harald · Oct 11, 2011

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.

Related questions

Retrieving the last record in each group - MySQL

There is a table messages that contains data as shown below: Id Name Other_Columns ------------------------- 1 A A_data_1 2 A A_data_2 3 A A_data_3 4 B B_data_1 5 B B_data_2 6 C C_data_1 If I run a query select * …

Read More
MySQL Query GROUP BY day / month / year

Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMP field, like: SELECT COUNT(id) FROM stats WHERE record_date.…

Read More
Error related to only_full_group_by when executing a query in MySql

I have upgraded my system and have installed MySql 5.7.9 with php for a web application I am working on. I have a query that is dynamically created, and when run in older versions of MySql it works fine. Since upgrading …

Read More