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 1 may help you link text

Have you tried putting the order in the 1 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

0

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 2 be ordered by cnt and with sub-totals in the 1 last row:

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

The two subqueries solution is needlessly 3 complicated. You just need one, and no 2 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 1 at the end.

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

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

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

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

We probably don't 8 want that, using the new keyword GROUPING() we 7 are able to sort items by their groupings 6 first and then within each group by individual 5 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 4 is an updated version that gets you what 3 you need and has the added benefit of making 2 sure that the ROLLUP appears where you want 1 it.