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)

Answer 1

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

Answer 2

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