[MySQL 5.0.45-community-nt-log] Sort the “rollup” in group by

Databases
1

Hello guys!

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 my way?

SELECT
	COALESCE (name, 'GRAND TOTAL') AS name,
	tot
FROM
	(
		SELECT
			name,
			COUNT(*) AS tot
		FROM
			tbl_p

		)
		GROUP BY
			name WITH ROLLUP
	) Q
ORDER BY
	CASE
WHEN name = 'GRAND TOTAL' THEN
	1
ELSE
	0
END;

Output:

+------------------------------+--------+
| name                         | tot    |
+------------------------------+--------+
| SEAN DUBOIS                  |      3 |
| SEAN MAXWELL                 |      2 |
| GRAND TOTAL                  |     39 |
| DILLINGER BOY                |     12 |
| DILLINGER GIRL               |     22 |
+------------------------------+--------+

I need this output:

+------------------------------+--------+
| name                         | tot    |
+------------------------------+--------+
| GRAND TOTAL                  |     39 |
| DILLINGER BOY                |     12 |
| DILLINGER GIRL               |     22 |
| SEAN DUBOIS                  |      3 |
| SEAN MAXWELL                 |      2 |
+------------------------------+--------+

Could you please help?
Thanks you very much for your help.

2 
ORDER
    BY CASE WHEN name = 'GRAND TOTAL'
            THEN 0
            ELSE 1 END
     , name
3

Thank you.

But:

SELECT
	COALESCE (name, 'GRAND TOTAL') AS name,
	tot
FROM
	(
		SELECT
			name,
			COUNT(*) AS tot
		FROM
			tbl_p

		)
		GROUP BY
			name WITH ROLLUP
	) Q
ORDER
BY CASE WHEN name = 'GRAND TOTAL'
THEN 0
ELSE 1 END, name;

Output:

+------------------------------+--------+
| name                         | tot    |
+------------------------------+--------+
| SEAN DUBOIS                  |      3 |
| SEAN MAXWELL                 |      2 |
| GRAND TOTAL                  |     39 |
| DILLINGER BOY                |     12 |
| DILLINGER GIRL               |     22 |
+------------------------------+--------+

:frowning:

4

use

COALESCE(name,'GRAND TOTAL') [COLOR="#0000FF"]AS print_name[/COLOR]

and then use print_name in the ORDER BY

5

Thank you.

But:

SELECT
	COALESCE (name, 'GRAND TOTAL') AS print_name,
	tot
FROM
	(
		SELECT
			name,
			COUNT(*) AS tot
		FROM
			tbl_p

		)
		GROUP BY
			name WITH ROLLUP
	) Q
ORDER
BY CASE WHEN name = 'GRAND TOTAL'
THEN 0
ELSE 1 END, print_name;

Output:

+------------------------------+--------+
| print_name                   | tot    |
+------------------------------+--------+
| SEAN DUBOIS                  |      3 |
| SEAN MAXWELL                 |      2 |
| GRAND TOTAL                  |     39 |
| DILLINGER BOY                |     12 |
| DILLINGER GIRL               |     22 |
+------------------------------+--------+

:frowning:

6

you must also use print_name inside the CASE expression

7

Thanks a lot! :slight_smile:

mysql> SELECT
	COALESCE (`name`, 'GRAND TOTAL') AS `print_name`,
	tot
FROM
	(
		SELECT
			`name`,
			COUNT(*) AS tot
		FROM
			tbl_p
		GROUP BY
			`name` WITH ROLLUP
	) Q
ORDER BY
	CASE
WHEN `print_name` = 'GRAND TOTAL' THEN
	0
ELSE
	1
END,
 `print_name`;
 
+----------------+-----+
| print_name     | tot |
+----------------+-----+
| GRAND TOTAL    |  39 |
| DILLINGER BOY  |  12 |
| DILLINGER GIRL |  22 |
| SEAN DUBOIS    |   3 |
| SEAN MAXWELL   |   2 |
+----------------+-----+
5 rows in set