# [ACCEPTED]-sort the "rollup" in group by-sql

Score: 17

try like using temporary table

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

Score: 14

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

Score: 3

You can try this query:

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

0

Score: 1

# 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)
``````
Score: 1

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.

Score: 0

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.

More Related questions