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