Performing a WITH ROLLUP when grouping by multiple fields, MySQL returns a rollup row for each group. I'm interested in the rollups for fields number and perc in dotable below:
+------+---------------+--------+-------+--------+------------+
| RDT | type | number | total | perc | thedate |
+------+---------------+--------+-------+--------+------------+
| MAL | checking | 112 | 3249 | 3.4 | 2014-03-27 |
| MAL | control | 33 | 3249 | 1.0 | 2014-03-27 |
| MAL | reconstructed | 5 | 3249 | 0.2 | 2014-03-27 |
| MAL | regular | 960 | 3249 | 29.5 | 2014-03-27 |
| MAL | study | 10 | 3249 | 0.3 | 2014-03-27 |
| tot | NULL | 1120 | 3249 | 34.0 | 2014-03-27 |
| PMC | checking | 107 | 3153 | 3.4 | 2014-03-27 |
| PMC | control | 167 | 3153 | 5.3 | 2014-03-27 |
| PMC | reconstructed | 8 | 3153 | 0.3 | 2014-03-27 |
| PMC | regular | 833 | 3153 | 26.4 | 2014-03-27 |
| PMC | study | 72 | 3153 | 2.3 | 2014-03-27 |
| tot | NULL | 1187 | 3249 | 36.5 | 2014-03-27 |
| RAS | checking | 1 | 970 | 0.1 | 2014-03-27 |
| RAS | control | 42 | 970 | 4.3 | 2014-03-27 |
| RAS | reconstructed | 1 | 970 | 0.1 | 2014-03-27 |
| RAS | regular | 318 | 970 | 32.8 | 2014-03-27 |
| RAS | study | 3 | 970 | 0.3 | 2014-03-27 |
| tot | NULL | 365 | 970 | 37.6 | 2014-03-27 |
| UOT | checking | 11 | 3527 | 0.3 | 2014-03-27 |
| UOT | control | 283 | 3527 | 8.0 | 2014-03-27 |
| UOT | regular | 235 | 3527 | 6.7 | 2014-03-27 |
| UOT | study | 8 | 3527 | 0.2 | 2014-03-27 |
| tot | NULL | 537 | 3527 | 15.2 | 2014-03-27 |
+------+---------------+--------+-------+--------+------------+
And I tried this query:
mysql> SELECT
IFNULL(`RDT`, 'tot') AS RDT,
`type`,
`number`,
`total`,
`perc`,
`thedate`
FROM
(
SELECT
`RDT`,
`type`,
`NUMBER`,
`total`,
`perc`,
`THEDATE`
FROM
`dotable`
GROUP BY
RDT,
`type`
ORDER BY
CASE
WHEN RDT = 'UOT' THEN
1
END ASC
) AS X
GROUP BY
X.RDT,
X.`type` WITH ROLLUP;
But the output is wrong, can you help me? the dotable structure and rows is below.
+------+---------------+--------+-------+--------+------------+
| RDT | type | number | total | perc | thedate |
+------+---------------+--------+-------+--------+------------+
| MAL | checking | 112 | 3249 | 3.4 | 2014-03-27 |
| MAL | control | 33 | 3249 | 1.0 | 2014-03-27 |
| MAL | reconstructed | 5 | 3249 | 0.2 | 2014-03-27 |
| MAL | regular | 960 | 3249 | 29.5 | 2014-03-27 |
| MAL | study | 10 | 3249 | 0.3 | 2014-03-27 |
| MAL | NULL | 10 | 3249 | 0.3 | 2014-03-27 |
| PMC | checking | 107 | 3153 | 3.4 | 2014-03-27 |
| PMC | control | 167 | 3153 | 5.3 | 2014-03-27 |
| PMC | reconstructed | 8 | 3153 | 0.3 | 2014-03-27 |
| PMC | regular | 833 | 3153 | 26.4 | 2014-03-27 |
| PMC | study | 72 | 3153 | 2.3 | 2014-03-27 |
| PMC | NULL | 72 | 3153 | 2.3 | 2014-03-27 |
| RAS | checking | 1 | 970 | 0.1 | 2014-03-27 |
| RAS | control | 42 | 970 | 4.3 | 2014-03-27 |
| RAS | reconstructed | 1 | 970 | 0.1 | 2014-03-27 |
| RAS | regular | 318 | 970 | 32.8 | 2014-03-27 |
| RAS | study | 3 | 970 | 0.3 | 2014-03-27 |
| RAS | NULL | 3 | 970 | 0.3 | 2014-03-27 |
| UOT | checking | 11 | 3527 | 0.3 | 2014-03-27 |
| UOT | control | 283 | 3527 | 8.0 | 2014-03-27 |
| UOT | regular | 235 | 3527 | 6.7 | 2014-03-27 |
| UOT | study | 8 | 3527 | 0.2 | 2014-03-27 |
| UOT | NULL | 8 | 3527 | 0.2 | 2014-03-27 |
| tot | NULL | 8 | 3527 | 0.2 | 2014-03-27 |
+------+---------------+--------+-------+--------+------------+
24 rows in set
mysql>
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `dotable`
-- ----------------------------
DROP TABLE IF EXISTS `dotable`;
CREATE TABLE `dotable` (
`RDT` varchar(10) DEFAULT NULL,
`TYPE` varchar(50) DEFAULT NULL,
`NUMBER` int(11) NOT NULL DEFAULT '0',
`TOTAL` int(11) NOT NULL DEFAULT '0',
`PERC` decimal(10,2) DEFAULT NULL,
`THEDATE` date DEFAULT NULL,
`ID` int(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of dotable
-- ----------------------------
INSERT INTO `dotable` VALUES ('UOT', 'control', '283', '3527', '8.0', '2014-03-27', '1');
INSERT INTO `dotable` VALUES ('UOT', 'regular', '235', '3527', '6.7', '2014-03-27', '2');
INSERT INTO `dotable` VALUES ('UOT', 'study', '8', '3527', '0.2', '2014-03-27', '3');
INSERT INTO `dotable` VALUES ('UOT', 'checking', '11', '3527', '0.3', '2014-03-27', '4');
INSERT INTO `dotable` VALUES ('MAL', 'regular', '960', '3249', '29.5', '2014-03-27', '5');
INSERT INTO `dotable` VALUES ('MAL', 'study', '10', '3249', '0.3', '2014-03-27', '6');
INSERT INTO `dotable` VALUES ('MAL', 'control', '33', '3249', '1.0', '2014-03-27', '7');
INSERT INTO `dotable` VALUES ('MAL', 'reconstructed', '5', '3249', '0.2', '2014-03-27', '8');
INSERT INTO `dotable` VALUES ('MAL', 'checking', '112', '3249', '3.4', '2014-03-27', '9');
INSERT INTO `dotable` VALUES ('PMC', 'regular', '833', '3153', '26.4', '2014-03-27', '10');
INSERT INTO `dotable` VALUES ('PMC', 'study', '72', '3153', '2.3', '2014-03-27', '11');
INSERT INTO `dotable` VALUES ('PMC', 'control', '167', '3153', '5.3', '2014-03-27', '12');
INSERT INTO `dotable` VALUES ('PMC', 'checking', '107', '3153', '3.4', '2014-03-27', '13');
INSERT INTO `dotable` VALUES ('PMC', 'reconstructed', '8', '3153', '0.3', '2014-03-27', '14');
INSERT INTO `dotable` VALUES ('RAS', 'reconstructed', '1', '970', '0.1', '2014-03-27', '15');
INSERT INTO `dotable` VALUES ('RAS', 'regular', '318', '970', '32.8', '2014-03-27', '16');
INSERT INTO `dotable` VALUES ('RAS', 'study', '3', '970', '0.3', '2014-03-27', '17');
INSERT INTO `dotable` VALUES ('RAS', 'control', '42', '970', '4.3', '2014-03-27', '18');
INSERT INTO `dotable` VALUES ('RAS', 'checking', '1', '970', '0.1', '2014-03-27', '19');
I'm interested in the rollups for fields number and perc in dotable.
So you need to do a sum() to make it work.
If you don't need the overall total we can also remove it.
For Version <= 5.7
Query:
select
if(`type` is null, 'tot', `rdt`) as `rdt`,
`type`,
`number`,
`total`,
`perc`,
`thedate`
from
(select
`rdt`,
`type`,
sum(`number`) as `number`,
`total`,
sum(`perc`) as `perc`,
`thedate`
from
`dotable`
group by `rdt`, `type` with rollup) as t
where
`rdt` is not null;
Result:
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 tot | null | 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 tot | null | 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 tot | null | 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 tot | null | 537 | 3527 | 15.20 | 2014-03-27
For Version > 5.7
Query:
select
if(`type` is null, 'tot', `rdt`) as `rdt`,
`type`,
sum(`number`) as `number`,
`total`,
sum(`perc`) as `perc`,
`thedate`
from
`dotable`
group by `rdt`, `type` with rollup
having not grouping(rdt) <> 0;
Result:
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 tot | null | 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 tot | null | 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 tot | null | 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 tot | null | 537 | 3527 | 15.20 | 2014-03-27
Fiddle:
db<>fiddle here
Note: When using group by without ERROR 1055 you need to set as
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));