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

[cms9651]

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.

[r937]

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

[cms9651]

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 |
+------------------------------+--------+

[r937]

use

COALESCE(name,'GRAND TOTAL') AS print_name

and then use print_name in the ORDER BY

[cms9651]

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 |
+------------------------------+--------+

[r937]

you must also use print_name inside the CASE expression

[cms9651]

you must also use print_name inside the CASE expression

Thanks a lot!

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