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