Hi, I can’t order by this query, why?
I need order for rdt value UOT first value, MAL second value, PMC three value and RAS four value.
Thanks in advance
mysql> SELECT
IFNULL(`RDT`, 'tot') AS RDT,
IFNULL(`type`, 'tot') AS `type`,
SUM(`number`) AS `number`,
`total`,
SUM(`perc`) AS `perc`,
`thedate`
FROM
(
SELECT
IFNULL(`RDT`, 'tot') AS RDT,
`type`,
`NUMBER`,
`total`,
`perc`,
`THEDATE`
FROM
`dotable`
GROUP BY
`RDT`,
`type`
ORDER BY
CASE
WHEN RDT LIKE 'UOT' THEN
1
WHEN RDT LIKE 'MAL' THEN
2
WHEN RDT LIKE 'PMC' THEN
3
ELSE
4
END, RDT
) AS X
GROUP BY
x.RDT,
x.type WITH ROLLUP;
+-----+---------------+--------+-------+--------+------------+
| RDT | type | number | total | perc | thedate |
+-----+---------------+--------+-------+--------+------------+
| MAL | checking | 112 | 3249 | 3.40 | 2014-03-27 |
| MAL | control | 33 | 3249 | 1.00 | 2014-03-27 |
| MAL | reconstructed | 5 | 3249 | 0.20 | 2014-03-27 |
| MAL | regular | 960 | 3249 | 29.50 | 2014-03-27 |
| MAL | study | 10 | 3249 | 0.30 | 2014-03-27 |
| MAL | tot | 1120 | 3249 | 34.40 | 2014-03-27 |
| PMC | checking | 107 | 3153 | 3.40 | 2014-03-27 |
| PMC | control | 167 | 3153 | 5.30 | 2014-03-27 |
| PMC | reconstructed | 8 | 3153 | 0.30 | 2014-03-27 |
| PMC | regular | 833 | 3153 | 26.40 | 2014-03-27 |
| PMC | study | 72 | 3153 | 2.30 | 2014-03-27 |
| PMC | tot | 1187 | 3153 | 37.70 | 2014-03-27 |
| RAS | checking | 1 | 970 | 0.10 | 2014-03-27 |
| RAS | control | 42 | 970 | 4.30 | 2014-03-27 |
| RAS | reconstructed | 1 | 970 | 0.10 | 2014-03-27 |
| RAS | regular | 318 | 970 | 32.80 | 2014-03-27 |
| RAS | study | 3 | 970 | 0.30 | 2014-03-27 |
| RAS | tot | 365 | 970 | 37.60 | 2014-03-27 |
| UOT | checking | 11 | 3527 | 0.30 | 2014-03-27 |
| UOT | control | 283 | 3527 | 8.00 | 2014-03-27 |
| UOT | regular | 235 | 3527 | 6.70 | 2014-03-27 |
| UOT | study | 8 | 3527 | 0.20 | 2014-03-27 |
| UOT | tot | 537 | 3527 | 15.20 | 2014-03-27 |
| tot | tot | 3209 | 3527 | 124.90 | 2014-03-27 |
+-----+---------------+--------+-------+--------+------------+
24 rows in set
mysql>
Okey Sir, but if ORDER BY on the outer query only I’ve error:
mysql> SELECT
IFNULL(`RDT`, 'tot') AS RDT,
IFNULL(`type`, 'tot') AS `type`,
SUM(`number`) AS `number`,
`total`,
SUM(`perc`) AS `perc`,
`thedate`
FROM
(
SELECT
IFNULL(`RDT`, 'tot') AS RDT,
`type`,
`NUMBER`,
`total`,
`perc`,
`THEDATE`
FROM
`dotable`
GROUP BY
`RDT`,
`type`
) AS X
GROUP BY
x.RDT,
x.type WITH ROLLUP
ORDER BY
FIND_IN_SET(RDT, 'UOT,MAL,PMC,RAS');
1221 - Incorrect usage of CUBE/ROLLUP and ORDER BY
mysql>
The last row of output is wrong in colum perc because show 124.90 value and not 90.98 value (numer/total)*100 = (3209/3527)*100
+-----+---------------+--------+-------+--------+------------+
| RDT | type | number | total | perc | thedate |
+-----+---------------+--------+-------+--------+------------+
| tot | rdt tot | 3209 | 3527 | 124.90 | 2014-03-27 |
+-----+---------------+--------+-------+--------+------------+