Search code examples
mysqlrollup

MYSQL Aggregation WITH ROLLUP and LIMIT

I need to get the first 10 rows per page, that returns the sum of values grouped by roomId. And have a footer to display the aggregated value, beyond limit.

SELECT  roomId, SUM(value) FROM table A
GROUP BY roomId
WITH ROLLUP
LIMIT 0,10

Issue is I need the summary generated by rollup to return the sum value of all the rooms, even those outside limit (page), (aggregation should not use limit)

Example data

+--------+------------+ 
| roomId | Sum(value) |
+========+============+
| 1      | 10         |
+--------+------------+
| 1      | 10         |
+--------+------------+
| 1      | 20         |
+--------+------------+
| 1      | 5          |
+--------+------------+
| 2      | 10         |
+--------+------------+
| 2      | 20         |
+--------+------------+
| 2      | 30         |
+--------+------------+
| 3      | 10         |
+--------+------------+
| 3      | 20         |
+--------+------------+
| NULL   | 135        |
+--------+------------+

However I'd like to get the rollup to sum the value of all the rooms not all just the displayed 10 first rows, the aggregation should sum all database rows, even if the user is just reading the first 10 pages.

so the rollup should look like:

+--------+------------+
| roomId | Sum(value) |
+========+============+
| ...    | ...        |
+--------+------------+
| NULL   | 58935      |
+--------+------------+

I know I can do 2 different queries, or use an UNION ALL, but is that true? Can't be done with rollup? Rollup would be quicker then using 2 different queries.

Solution

  • The difficult is that LIMIT is applied after the GROUP BY ... WITH ROLLUP, so the row with the NULL is just one row among the rows to be limited.

    One solution is to sort the rows so the rollup row is first. Then you can use LIMIT and you get the rollup row, and N-1 rows.

    mysql> SELECT roomId, SUM(value) FROM A GROUP BY roomId WITH ROLLUP 
       ORDER BY roomId LIMIT 3;
+--------+------------+
| roomId | SUM(value) |
+--------+------------+
|   NULL |        135 |
|      1 |         45 |
|      2 |         60 |
+--------+------------+

    But these rows may not be in the order you want to display. You'd have to fetch the results and manipulate the display order using application code.

    Also you must use MySQL 8.0.12 or later to use ORDER BY in a query with ROLLUP, according to the notes in the documentation: https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html

    If not using MySQL 8.0.12, then this can be done with a subquery:

    mysql> SELECT * FROM (SELECT roomId, SUM(value) FROM A GROUP BY roomId WITH ROLLUP) result ORDER BY roomId LIMIT 3;
+--------+------------+
| roomId | SUM(value) |
+--------+------------+
|   NULL |        300 |
|      1 |         25 |
|      2 |         25 |
+--------+------------+

    Re your comment:

    LIMIT is applied after aggregation, including after the ROLLUP calculation. So the total sum calculated by ROLLUP includes all the results, not just those that are included in the limited set of rows. That's what you say you want, right?

    Here's a demo:

    insert into A values (1, 10), (1, 15), (2, 10), (2, 15), (3, 10), (3, 15), (4, 10), (4, 15), (5, 10), (5, 15), (6, 10), (6, 15), (7, 10), (7, 15), (8, 10), (8, 15), (9, 10), (9, 15), (10, 10), (10, 15), (11, 10), (11, 15), (12, 10), (12, 15);

    Now there should be a sum of 25 per roomId, and for 12 rooms, the total sum should be 12 * 25 or 300.

    mysql> SELECT roomId, SUM(value) FROM A GROUP BY roomId WITH ROLLUP 
    ->        ORDER BY roomId LIMIT 3;
+--------+------------+
| roomId | SUM(value) |
+--------+------------+
|   NULL |        300 |
|      1 |         25 |
|      2 |         25 |
+--------+------------+

    If the ROLLUP were a total of only those that were within the LIMIT, the rollup sum should be 50, not 300. The above example shows that the rollup includes all of them, because aggregation is done before LIMIT.

    If you do want the rollup to include only those rooms in the limit, you'd have to do the LIMIT in a subquery, then re-do the aggregation with ROLLUP in the outer query so it applies only to those returned by the subquery.

    mysql> SELECT roomId, SUM(value) FROM (
    -> SELECT roomId, SUM(value) AS value FROM A
    ->  GROUP BY roomId ORDER BY roomId LIMIT 3) AS t 
    -> GROUP BY roomId WITH ROLLUP;
+--------+------------+
| roomId | SUM(value) |
+--------+------------+
|      1 |         50 |
|      2 |         50 |
|      3 |         50 |
|   NULL |        150 |
+--------+------------+