sort the "rollup" in group by

Question

I found that the "with rollup" option used with group by is very useful. But it does not behave with "order by" clause. Is there any way to order by the way I want as well as calculate the sub-totals?

CREATE TABLE `mygroup` (
  `id` int(11) default NULL,
  `country` varchar(100) default NULL
) ENGINE=MyISAM ;

INSERT INTO `mygroup` VALUES (1,'India'),(5,'India'),(8,'India'),(18,'China'),(28,'China'),(28,'China');

mysql>select country, sum(id) from mygroup group by country with rollup; 
+---------+---------+
| country | sum(id) |
+---------+---------+
| China   |      74 | 
| India   |      14 | 
| NULL    |      88 | 
+---------+---------+
3 rows in set (0.00 sec)

mysql>select country, sum(id) as cnt from mygroup group by country order by cnt ;
+---------+------+
| country | cnt  |
+---------+------+
| India   |   14 | 
| China   |   74 | 
+---------+------+
2 rows in set (0.00 sec)

mysql>select country, sum(id) as cnt from mygroup group by country with rollup order by cnt;
ERROR 1221 (HY000): Incorrect usage of CUBE/ROLLUP and ORDER BY

Expected Result:
+---------+------+
| country | cnt  |
+---------+------+
| India   |   14 | 
| China   |   74 | 
| NULL    |   88 | 
+---------+---------+
3 rows in set (0.00 sec)

score 17 · Accepted Answer · edited Feb 22 '12 at 13:03

17

try like using temporary table

 SELECT * 
 FROM 
 (
     SELECT country, sum(id) as cnt 
     FROM mygroup GROUP BY country WITH rollup
 ) t 
 ORDER BY cnt;

This article may help you link text

edited Feb 22 '12 at 13:03

Mahmoud Gamal

78,257
17
139
164

answered Nov 20 '09 at 05:49

valli

5,797
2
19
9

3

The actual query has several columns in the group by clause. using your suggestion is ordering the subtotals and that is not desirable. – shantanuo Nov 20 '09 at 05:57

score 14 · Answer 2 · answered Sep 25 '12 at 17:20

14

Have you tried putting the order in the grouping?

SELECT country, SUM(id) 
    FROM mygroup 
GROUP BY country DESC WITH ROLLUP;

Should return:

+---------+---------+
| country | SUM(id) |
+---------+---------+
| India   |      14 |
| China   |      74 |
| NULL    |      88 |
+---------+---------+

http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

answered Sep 25 '12 at 17:20

sig11

541
5
13

3

Group by clause is by default sorted. I need to sort on sum(id) – shantanuo Sep 27 '12 at 01:25
AH! Sorry I misunderstood the question! It all becomes clear now. :) – sig11 Sep 27 '12 at 20:59

score 3 · Answer 3 · edited Nov 02 '15 at 09:13

3

You can try this query:

 SELECT country,id
     FROM mygroup GROUP BY country ASC WITH ROLLUP

edited Nov 02 '15 at 09:13

veljasije

6,722
12
48
79

answered Nov 02 '15 at 07:43

vivek s vamja

1,001
10
11

This is the best answer as sub-queries are bad for performance on large data-sets. – Derokorian May 06 '16 at 17:30
3

it doesnt answer the OP who needs to sort by the rollup sum, not category – Eddie Sep 02 '16 at 18:00
@Derokorian an answer that doesn't work can't be best. and sub-queries are often the most efficient way to do something; it all depends on many many things. – ysth Jan 31 '19 at 04:44

Rockallite · Answer 4 · 2017-06-14T08:07:40.053

Solution

Use two sub-queries, like this:

-- 3. Filter the redundant rows. Don't use HAVING; it produces a redundant row
--    when there's only one country.
SELECT r2.country, r2.cnt
FROM (
    -- 2. Save the ordering by giving each row an increasing serial number. 
    --    By grouping by serial numbers and country counts with rollup, the
    --    ordering is preserved and rollup data is calculated.
    SELECT (@s := @s + 1) AS sn, r1.country, SUM(r1.cnt) AS cnt
    FROM (
        -- 1. Calculate the country counts and order by them
        SELECT country, SUM(id) AS cnt
        FROM mygroup
        GROUP BY 1
        ORDER BY 2
    ) r1, (SELECT @s := 0) x
    GROUP BY 1, 2 WITH ROLLUP
) r2
WHERE r2.country IS NOT NULL OR r2.sn IS NULL

Result should be ordered by cnt and with sub-totals in the last row:

+---------+------+
| country | cnt  |
+---------+------+
| India   |   14 | 
| China   |   74 | 
| NULL    |   88 | 
+---------+------+
3 rows in set (0.00 sec)

score 1 · Answer 5 · answered Jan 31 '19 at 04:51

The two subqueries solution is needlessly complicated. You just need one, and no serial number.

select country, cnt
from (
    select
        country,
        sum(id) as cnt
    from mygroup
    group by country with rollup
) mygroup_with_rollup
order by country is null, cnt, country;

The country is null places the rollup row at the end.

score 0 · Answer 6 · answered Oct 17 '20 at 08:28

It would appear that since 2017 MySQL has had the GROUPING() function. It works when using ORDER BY. Furthermore, GROUP BY and ORDER BY can now co-exist (your original question's 3rd query no longer throws an error), but it does have some flaws when trying to sort data, typically just that it throws the ROLLUP to the top of the table when DESC, but there are other issues to that I will demonstrate and then show how to fix it. Let's throw a wrench into the original data:

INSERT INTO `mygroup` VALUES (-8,'Kenya'),(-12,'Kenya');

Using the temporary table method shown above, you'll have these results where the NULL/ROLLUP appears in the middle of the table, which is another problem, probably even worse than it going to the top:

+---------+------+
| country | cnt  |
+---------+------+
| Kenya   |  -20 |
| India   |   14 |
| NULL    |   68 |
| China   |   74 |
+---------+------+

We probably don't want that, using the new keyword GROUPING() we are able to sort items by their groupings first and then within each group by individual columns:

-- big numbers on bottom, middle-numbered ROLLUP last
SELECT country, SUM(id) AS cnt FROM mygroup
GROUP BY country WITH ROLLUP ORDER BY GROUPING(country), cnt ASC;

-- big numbers on top, middle-numbered ROLLUP still last
SELECT country, SUM(id) AS cnt FROM mygroup
GROUP BY country WITH ROLLUP ORDER BY GROUPING(country), cnt DESC;

Results (of DESC):

+---------+------+
| country | cnt  |
+---------+------+
| China   |   74 |
| India   |   14 |
| Kenya   |  -20 |
| NULL    |   68 |
+---------+------+

The GROUPING() method is an updated version that gets you what you need and has the added benefit of making sure that the ROLLUP appears where you want it.

sort the "rollup" in group by

6 Answers6

Solution

Linked