Code:
mysql> SELECT
Qst,
IFNULL(Strd, 'Tot') AS Strd,
IFNULL(activated, 'Tot') AS Dimension,
sums.E1,
sums.E2,
sums.E3,
sums.E4,
sums.E5,
sums.E6,
sums.E1 + sums.E2 + sums.E3 + sums.E4 + sums.E5 + sums.E6 AS SumsE,
ROUND(
(
(sums.E1 * 1) + (sums.E2 * 2) + (sums.E3 * 3) + (sums.E4 * 4) + (sums.E5 * 5) + (sums.E6 * 6)
) / (
sums.E1 + sums.E2 + sums.E3 + sums.E4 + sums.E5 + sums.E6
),
1
) AS new_output,
Qst_prest
FROM
(
SELECT
COUNT(*) AS Qst,
Strd,
activated,
SUM(IF(E = 1, 1, 0)) AS 'E1',
SUM(IF(E = 2, 1, 0)) AS 'E2',
SUM(IF(E = 3, 1, 0)) AS 'E3',
SUM(IF(E = 4, 1, 0)) AS 'E4',
SUM(IF(E = 5, 1, 0)) AS 'E5',
SUM(IF(E = 6, 1, 0)) AS 'E6',
B.Qst AS Qst_prest
FROM
tbl_A A
LEFT OUTER JOIN tbl_B B ON A.activated = B.prest
AND A.Strd = B.Strds
WHERE
E IS NOT NULL
AND activated = 'LavC'
GROUP BY
Strd,
activated,
qst WITH ROLLUP
) AS sums;
+-----+------+-----------+----+----+----+----+----+----+-------+------------+-----------+
| Qst | Strd | Dimension | E1 | E2 | E3 | E4 | E5 | E6 | SumsE | new_output | Qst_prest |
+-----+------+-----------+----+----+----+----+----+----+-------+------------+-----------+
| 2 | BBB | LavC | 0 | 0 | 0 | 0 | 2 | 0 | 2 | 5.0 | 134 |
| 2 | BBB | LavC | 0 | 0 | 0 | 0 | 2 | 0 | 2 | 5.0 | 134 |
| 2 | BBB | Tot | 0 | 0 | 0 | 0 | 2 | 0 | 2 | 5.0 | 134 |
| 2 | CCC | LavC | 0 | 0 | 0 | 2 | 0 | 0 | 2 | 4.0 | 99 |
| 2 | CCC | LavC | 0 | 0 | 0 | 2 | 0 | 0 | 2 | 4.0 | 99 |
| 2 | CCC | Tot | 0 | 0 | 0 | 2 | 0 | 0 | 2 | 4.0 | 99 |
| 1 | DDD | LavC | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 5.0 | 75 |
| 1 | DDD | LavC | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 5.0 | 75 |
| 1 | DDD | Tot | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 5.0 | 75 |
| 5 | Tot | Tot | 0 | 0 | 0 | 2 | 3 | 0 | 5 | 4.6 | 75 |
+-----+------+-----------+----+----+----+----+----+----+-------+------------+-----------+
10 rows in set
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tbl_a`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_a`;
CREATE TABLE `tbl_a` (
`activated` varchar(255) DEFAULT NULL,
`Strd` varchar(255) DEFAULT NULL,
`E` int(10) DEFAULT NULL,
`id` int(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=160 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of tbl_a
-- ----------------------------
INSERT INTO `tbl_a` VALUES ('RiprG', 'CCC', null, '1');
INSERT INTO `tbl_a` VALUES ('RiprG', 'CCC', null, '2');
INSERT INTO `tbl_a` VALUES ('RiprG', 'CCC', '5', '3');
INSERT INTO `tbl_a` VALUES ('RiprG', 'CCC', null, '4');
INSERT INTO `tbl_a` VALUES ('RiprG', 'CCC', '5', '5');
INSERT INTO `tbl_a` VALUES ('RiprG', 'CCC', null, '6');
INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', '3', '7');
INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', '5', '8');
INSERT INTO `tbl_a` VALUES ('PredS', 'CCC', '4', '9');
INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', '4', '10');
INSERT INTO `tbl_a` VALUES ('PredS', 'CCC', '1', '11');
INSERT INTO `tbl_a` VALUES ('PredS', 'CCC', '5', '12');
INSERT INTO `tbl_a` VALUES ('PredS', 'CCC', '5', '13');
INSERT INTO `tbl_a` VALUES ('Act', 'CCC', null, '14');
INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '4', '15');
INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '4', '16');
INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '4', '17');
INSERT INTO `tbl_a` VALUES ('Act', 'CCC', null, '18');
INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', '5', '19');
INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', '5', '20');
INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', null, '21');
INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', null, '22');
INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', '5', '23');
INSERT INTO `tbl_a` VALUES ('PredS', 'CCC', '5', '24');
INSERT INTO `tbl_a` VALUES ('PredS', 'CCC', null, '25');
INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', null, '26');
INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', null, '27');
INSERT INTO `tbl_a` VALUES ('PredS', 'CCC', null, '28');
INSERT INTO `tbl_a` VALUES ('Act', 'CCC', null, '29');
INSERT INTO `tbl_a` VALUES ('Act', 'CCC', null, '30');
INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '3', '31');
INSERT INTO `tbl_a` VALUES ('LavC', 'CCC', null, '32');
INSERT INTO `tbl_a` VALUES ('PredS', 'CCC', null, '33');
INSERT INTO `tbl_a` VALUES ('PredS', 'CCC', '5', '34');
INSERT INTO `tbl_a` VALUES ('LavC', 'CCC', '4', '35');
INSERT INTO `tbl_a` VALUES ('LavC', 'CCC', null, '36');
INSERT INTO `tbl_a` VALUES ('LavC', 'CCC', '4', '37');
INSERT INTO `tbl_a` VALUES ('RiprG', 'CCC', '5', '38');
INSERT INTO `tbl_a` VALUES ('RiprG', 'CCC', '5', '39');
INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '4', '40');
INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '5', '41');
INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '5', '42');
INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '5', '43');
INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '5', '44');
INSERT INTO `tbl_a` VALUES ('RiprG', 'BBB', '3', '45');
INSERT INTO `tbl_a` VALUES ('RiprG', 'BBB', null, '46');
INSERT INTO `tbl_a` VALUES ('RiprG', 'BBB', null, '47');
INSERT INTO `tbl_a` VALUES ('RiprG', 'BBB', null, '48');
INSERT INTO `tbl_a` VALUES ('Act', 'BBB', '5', '49');
INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', '5', '50');
INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', null, '51');
INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', '4', '52');
INSERT INTO `tbl_a` VALUES ('Act', 'BBB', null, '53');
INSERT INTO `tbl_a` VALUES ('Act', 'BBB', '4', '54');
INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', null, '55');
INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', '5', '56');
INSERT INTO `tbl_a` VALUES ('Act', 'BBB', null, '57');
INSERT INTO `tbl_a` VALUES ('Act', 'BBB', '4', '58');
INSERT INTO `tbl_a` VALUES ('LavC', 'BBB', null, '59');
INSERT INTO `tbl_a` VALUES ('Act', 'BBB', null, '60');
INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', '5', '61');
INSERT INTO `tbl_a` VALUES ('LavC', 'BBB', null, '62');
INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', '5', '63');
INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', null, '64');
INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', null, '65');
INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', '5', '66');
INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', null, '67');
INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', '5', '68');
INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', '4', '69');
INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', null, '70');
INSERT INTO `tbl_a` VALUES ('Act', 'BBB', '5', '71');
INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', null, '72');
INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', null, '73');
INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', null, '74');
INSERT INTO `tbl_a` VALUES ('Act', 'BBB', '5', '75');
INSERT INTO `tbl_a` VALUES ('Act', 'BBB', '5', '76');
INSERT INTO `tbl_a` VALUES ('Act', 'BBB', null, '77');
INSERT INTO `tbl_a` VALUES ('RiprG', 'AAA', null, '78');
INSERT INTO `tbl_a` VALUES ('RiprG', 'AAA', '5', '79');
INSERT INTO `tbl_a` VALUES ('RiprG', 'AAA', '3', '80');
INSERT INTO `tbl_a` VALUES ('RiprG', 'AAA', '4', '81');
INSERT INTO `tbl_a` VALUES ('RiprG', 'AAA', null, '82');
INSERT INTO `tbl_a` VALUES ('LavS', 'AAA', '5', '83');
INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', null, '84');
INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', null, '85');
INSERT INTO `tbl_a` VALUES ('LavS', 'AAA', '3', '86');
INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', '5', '87');
INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', null, '88');
INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', '4', '89');
INSERT INTO `tbl_a` VALUES ('LavS', 'AAA', null, '90');
INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', null, '91');
INSERT INTO `tbl_a` VALUES ('LavS', 'AAA', '5', '92');
INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', null, '93');
INSERT INTO `tbl_a` VALUES ('Act', 'AAA', '4', '94');
INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', '5', '95');
INSERT INTO `tbl_a` VALUES ('Act', 'AAA', '5', '96');
INSERT INTO `tbl_a` VALUES ('Act', 'AAA', null, '97');
INSERT INTO `tbl_a` VALUES ('Act', 'AAA', null, '98');
INSERT INTO `tbl_a` VALUES ('Act', 'AAA', '5', '99');
INSERT INTO `tbl_a` VALUES ('Act', 'AAA', null, '100');
INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', '5', '101');
INSERT INTO `tbl_a` VALUES ('Act', 'AAA', '4', '102');
INSERT INTO `tbl_a` VALUES ('Act', 'AAA', '4', '103');
INSERT INTO `tbl_a` VALUES ('Act', 'AAA', '5', '104');
INSERT INTO `tbl_a` VALUES ('Act', 'AAA', '4', '105');
INSERT INTO `tbl_a` VALUES ('Act', 'AAA', null, '106');
INSERT INTO `tbl_a` VALUES ('Act', 'AAA', null, '107');
INSERT INTO `tbl_a` VALUES ('RiprG', 'AAA', '5', '108');
INSERT INTO `tbl_a` VALUES ('Act', 'AAA', null, '109');
INSERT INTO `tbl_a` VALUES ('RiprG', 'BBB', '4', '110');
INSERT INTO `tbl_a` VALUES ('LavC', 'BBB', '5', '111');
INSERT INTO `tbl_a` VALUES ('LavC', 'BBB', '5', '112');
INSERT INTO `tbl_a` VALUES ('Act', 'BBB', '5', '113');
INSERT INTO `tbl_a` VALUES ('Act', 'BBB', '4', '114');
INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', '5', '115');
INSERT INTO `tbl_a` VALUES ('LavS', 'BBB', '5', '116');
INSERT INTO `tbl_a` VALUES ('Act', 'BBB', '5', '117');
INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', '5', '118');
INSERT INTO `tbl_a` VALUES ('PredS', 'BBB', '3', '119');
INSERT INTO `tbl_a` VALUES ('LavS', 'BBB', '1', '120');
INSERT INTO `tbl_a` VALUES ('LavS', 'BBB', '5', '121');
INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', '4', '122');
INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '4', '123');
INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '5', '124');
INSERT INTO `tbl_a` VALUES ('PredC', 'AAA', '4', '125');
INSERT INTO `tbl_a` VALUES ('Act', 'AAA', '4', '126');
INSERT INTO `tbl_a` VALUES ('Act', 'AAA', null, '127');
INSERT INTO `tbl_a` VALUES ('Act', 'AAA', '5', '128');
INSERT INTO `tbl_a` VALUES ('Act', 'AAA', '4', '129');
INSERT INTO `tbl_a` VALUES ('LavS', 'AAA', '5', '130');
INSERT INTO `tbl_a` VALUES ('LavS', 'AAA', '5', '131');
INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', '4', '132');
INSERT INTO `tbl_a` VALUES ('Act', 'AAA', '4', '133');
INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', '4', '134');
INSERT INTO `tbl_a` VALUES ('PredS', 'AAA', '5', '135');
INSERT INTO `tbl_a` VALUES ('LavS', 'CCC', '4', '136');
INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '4', '137');
INSERT INTO `tbl_a` VALUES ('Act', 'CCC', '5', '138');
INSERT INTO `tbl_a` VALUES ('RiprG', 'CCC', '4', '139');
INSERT INTO `tbl_a` VALUES ('RiprG', 'DDD', '5', '140');
INSERT INTO `tbl_a` VALUES ('Act', 'DDD', '5', '141');
INSERT INTO `tbl_a` VALUES ('Act', 'DDD', '5', '142');
INSERT INTO `tbl_a` VALUES ('Act', 'DDD', '1', '143');
INSERT INTO `tbl_a` VALUES ('Act', 'DDD', '4', '144');
INSERT INTO `tbl_a` VALUES ('Act', 'DDD', null, '145');
INSERT INTO `tbl_a` VALUES ('LavS', 'DDD', '5', '146');
INSERT INTO `tbl_a` VALUES ('PredS', 'DDD', '5', '147');
INSERT INTO `tbl_a` VALUES ('PredS', 'DDD', '5', '148');
INSERT INTO `tbl_a` VALUES ('PredS', 'DDD', '5', '149');
INSERT INTO `tbl_a` VALUES ('LavS', 'DDD', '5', '150');
INSERT INTO `tbl_a` VALUES ('LavS', 'DDD', null, '151');
INSERT INTO `tbl_a` VALUES ('RiprG', 'BBB', '5', '152');
INSERT INTO `tbl_a` VALUES ('RiprG', 'BBB', '5', '153');
INSERT INTO `tbl_a` VALUES ('RiprG', 'DDD', '5', '154');
INSERT INTO `tbl_a` VALUES ('RiprG', 'DDD', '5', '155');
INSERT INTO `tbl_a` VALUES ('LavS', 'DDD', '5', '156');
INSERT INTO `tbl_a` VALUES ('LavC', 'DDD', '5', '157');
INSERT INTO `tbl_a` VALUES ('LavS', 'DDD', '5', '158');
INSERT INTO `tbl_a` VALUES ('LavC', 'AAA', null, '159');
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tbl_b`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_b`;
CREATE TABLE `tbl_b` (
`Strds` char(3) DEFAULT NULL,
`prest` char(50) DEFAULT NULL,
`Qst` int(11) DEFAULT NULL,
`id` int(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of tbl_b
-- ----------------------------
INSERT INTO `tbl_b` VALUES ('BBB', 'Act', '493', '1');
INSERT INTO `tbl_b` VALUES ('BBB', 'LavS', '787', '2');
INSERT INTO `tbl_b` VALUES ('BBB', 'LavC', '134', '3');
INSERT INTO `tbl_b` VALUES ('BBB', 'RiprG', '252', '4');
INSERT INTO `tbl_b` VALUES ('CCC', 'Act', '532', '5');
INSERT INTO `tbl_b` VALUES ('CCC', 'LavS', '887', '6');
INSERT INTO `tbl_b` VALUES ('CCC', 'LavC', '99', '7');
INSERT INTO `tbl_b` VALUES ('CCC', 'RiprG', '457', '8');
INSERT INTO `tbl_b` VALUES ('AAA', 'Act', '729', '9');
INSERT INTO `tbl_b` VALUES ('AAA', 'LavS', '976', '10');
INSERT INTO `tbl_b` VALUES ('AAA', 'LavC', '110', '11');
INSERT INTO `tbl_b` VALUES ('AAA', 'RiprG', '312', '12');
INSERT INTO `tbl_b` VALUES ('DDD', 'Act', '206', '13');
INSERT INTO `tbl_b` VALUES ('DDD', 'LavS', '338', '14');
INSERT INTO `tbl_b` VALUES ('DDD', 'LavC', '75', '15');
INSERT INTO `tbl_b` VALUES ('DDD', 'RiprG', '151', '16');