Sort collapse in group by

Question

I found that the “with rollup” option used with the group is very useful. But he does not behave with the condition "order". Is there any way to order, by the way, I want, as well as calculate the total results?

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 using a temporary table

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

This article can help you link text.

Answer 2

Have you tried to place an order in a group?

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

Must return:

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

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

Answer 3

You can try this query:

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

Answer 4

Decision

Use two subqueries, for example:

 -- 3. Filter the redundant rows. Don't use HAVING; it produces a redundant row -- when there 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 

The result should be ordered by cnt and with sub-totals in the last line:

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

Answer 5

Solving two subqueries is unnecessarily complicated. You only need one, not a 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; 

country is null empty, at the end is a convolution line.