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): I开发者_运维百科ncorrect usage of CUBE/ROLLUP and ORDER BY

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

---

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

---

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

---

You can try this query:

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

---

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)

---

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.

---

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.