Hi all, hope in your help.
I need this output:
+----------+------+-------+
| question | mean | value |
+----------+------+-------+
| A1 | 6 | 2 |
| B1 | 10 | 11 |
| C1 | 82 | 84 |
| D1 | 2 | 2 |
+----------+------+-------+
I’ve tried this queries for units equal to units_2 and the output is incongruent, because this first query print this output:
mysql> SELECT
question,
ROUND(
value / (
SELECT
SUM(value)
FROM
`tbl_questions`
WHERE
1
AND units = 'units_2'
AND question IN ('A1', 'B1', 'C1', 'D1')
) * 100,
0
) AS value
FROM
`tbl_questions`
WHERE
1
AND units = 'units_2'
AND question IN ('A1', 'B1', 'C1', 'D1');
+----------+-------+
| question | value |
+----------+-------+
| A1 | 2 |
| B1 | 11 |
| C1 | 84 |
| D1 | 2 |
+----------+-------+
4 rows in set
but this second query print this other output when the values in value column not corresponding with the values in value column of first query:
mysql> SELECT
question,
ROUND(
SUM(value) / SUM(Participants) * 100,
0
) AS mean,
ROUND(
value / (
SELECT
SUM(value)
FROM
`tbl_questions`
WHERE
1
AND units = 'units_2'
AND question IN ('A1', 'B1', 'C1', 'D1')
) * 100,
0
) AS value
FROM
`tbl_questions`
WHERE
1
AND question IN ('A1', 'B1', 'C1', 'D1')
GROUP BY
question;
+----------+------+-------+
| question | mean | value |
+----------+------+-------+
| A1 | 6 | 9 |
| B1 | 10 | 9 |
| C1 | 82 | 82 |
| D1 | 2 | 2 |
+----------+------+-------+
4 rows in set
Appears in the second query are always extracts the values of units equals to units_1… can you help me?
Any suggestions would be appreciated.
Thanks in advance.
-- ----------------------------
-- Table structure for `tbl_questions`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_questions`;
CREATE TABLE `tbl_questions` (
`question` varchar(255) DEFAULT NULL,
`value` int(10) DEFAULT NULL,
`units` varchar(255) DEFAULT NULL,
`questiondate` date DEFAULT NULL,
`Participants` int(10) DEFAULT NULL,
`id` int(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=97 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of tbl_questions
-- ----------------------------
INSERT INTO `tbl_questions` VALUES ('A1', '4', 'units_1', '2013-05-28', '45', '1');
INSERT INTO `tbl_questions` VALUES ('A2', '15', 'units_1', '2013-05-28', '45', '2');
INSERT INTO `tbl_questions` VALUES ('A3', '12', 'units_1', '2013-05-28', '45', '3');
INSERT INTO `tbl_questions` VALUES ('A4', '31', 'units_1', '2013-05-28', '45', '4');
INSERT INTO `tbl_questions` VALUES ('A5', '9', 'units_1', '2013-05-28', '45', '5');
INSERT INTO `tbl_questions` VALUES ('A6', '19', 'units_1', '2013-05-28', '45', '6');
INSERT INTO `tbl_questions` VALUES ('A7', '18', 'units_1', '2013-05-28', '45', '7');
INSERT INTO `tbl_questions` VALUES ('A8', '6', 'units_1', '2013-05-28', '45', '8');
INSERT INTO `tbl_questions` VALUES ('A9', '17', 'units_1', '2013-05-28', '45', '9');
INSERT INTO `tbl_questions` VALUES ('A10', '21', 'units_1', '2013-05-28', '45', '10');
INSERT INTO `tbl_questions` VALUES ('A11', '14', 'units_1', '2013-05-28', '45', '11');
INSERT INTO `tbl_questions` VALUES ('A12', '16', 'units_1', '2013-05-28', '45', '12');
INSERT INTO `tbl_questions` VALUES ('A13', '43', 'units_1', '2013-05-28', '45', '13');
INSERT INTO `tbl_questions` VALUES ('B1', '4', 'units_1', '2013-05-28', '45', '14');
INSERT INTO `tbl_questions` VALUES ('B2', '3', 'units_1', '2013-05-28', '45', '15');
INSERT INTO `tbl_questions` VALUES ('B3', '18', 'units_1', '2013-05-28', '45', '16');
INSERT INTO `tbl_questions` VALUES ('B4', '2', 'units_1', '2013-05-28', '45', '17');
INSERT INTO `tbl_questions` VALUES ('B5', '18', 'units_1', '2013-05-28', '45', '18');
INSERT INTO `tbl_questions` VALUES ('B6', '9', 'units_1', '2013-05-28', '45', '19');
INSERT INTO `tbl_questions` VALUES ('B7', '6', 'units_1', '2013-05-28', '45', '20');
INSERT INTO `tbl_questions` VALUES ('B8', '5', 'units_1', '2013-05-28', '45', '21');
INSERT INTO `tbl_questions` VALUES ('B9', '5', 'units_1', '2013-05-28', '45', '22');
INSERT INTO `tbl_questions` VALUES ('B10', '8', 'units_1', '2013-05-28', '45', '23');
INSERT INTO `tbl_questions` VALUES ('B11', '14', 'units_1', '2013-05-28', '45', '24');
INSERT INTO `tbl_questions` VALUES ('B12', '12', 'units_1', '2013-05-28', '45', '25');
INSERT INTO `tbl_questions` VALUES ('B13', '2', 'units_1', '2013-05-28', '45', '26');
INSERT INTO `tbl_questions` VALUES ('C1', '36', 'units_1', '2013-05-28', '45', '27');
INSERT INTO `tbl_questions` VALUES ('C2', '2', 'units_1', '2013-05-28', '45', '28');
INSERT INTO `tbl_questions` VALUES ('C3', '4', 'units_1', '2013-05-28', '45', '29');
INSERT INTO `tbl_questions` VALUES ('C4', '12', 'units_1', '2013-05-28', '45', '30');
INSERT INTO `tbl_questions` VALUES ('C5', '7', 'units_1', '2013-05-28', '45', '31');
INSERT INTO `tbl_questions` VALUES ('C6', '14', 'units_1', '2013-05-28', '45', '32');
INSERT INTO `tbl_questions` VALUES ('C7', '18', 'units_1', '2013-05-28', '45', '33');
INSERT INTO `tbl_questions` VALUES ('C8', '19', 'units_1', '2013-05-28', '45', '34');
INSERT INTO `tbl_questions` VALUES ('C9', '23', 'units_1', '2013-05-28', '45', '35');
INSERT INTO `tbl_questions` VALUES ('C10', '3', 'units_1', '2013-05-28', '45', '36');
INSERT INTO `tbl_questions` VALUES ('C11', '8', 'units_1', '2013-05-28', '45', '37');
INSERT INTO `tbl_questions` VALUES ('C12', '17', 'units_1', '2013-05-28', '45', '38');
INSERT INTO `tbl_questions` VALUES ('C13', '0', 'units_1', '2013-05-28', '45', '39');
INSERT INTO `tbl_questions` VALUES ('D1', '1', 'units_1', '2013-05-28', '45', '40');
INSERT INTO `tbl_questions` VALUES ('D2', '25', 'units_1', '2013-05-28', '45', '41');
INSERT INTO `tbl_questions` VALUES ('D3', '11', 'units_1', '2013-05-28', '45', '42');
INSERT INTO `tbl_questions` VALUES ('D5', '11', 'units_1', '2013-05-28', '45', '43');
INSERT INTO `tbl_questions` VALUES ('D6', '3', 'units_1', '2013-05-28', '45', '44');
INSERT INTO `tbl_questions` VALUES ('D7', '3', 'units_1', '2013-05-28', '45', '45');
INSERT INTO `tbl_questions` VALUES ('D8', '15', 'units_1', '2013-05-28', '45', '46');
INSERT INTO `tbl_questions` VALUES ('D10', '13', 'units_1', '2013-05-28', '45', '47');
INSERT INTO `tbl_questions` VALUES ('D11', '9', 'units_1', '2013-05-28', '45', '48');
INSERT INTO `tbl_questions` VALUES ('A1', '1', 'units_2', '2013-05-29', '44', '49');
INSERT INTO `tbl_questions` VALUES ('A2', '9', 'units_2', '2013-05-29', '44', '50');
INSERT INTO `tbl_questions` VALUES ('A3', '8', 'units_2', '2013-05-29', '44', '51');
INSERT INTO `tbl_questions` VALUES ('A4', '29', 'units_2', '2013-05-29', '44', '52');
INSERT INTO `tbl_questions` VALUES ('A5', '5', 'units_2', '2013-05-29', '44', '53');
INSERT INTO `tbl_questions` VALUES ('A6', '15', 'units_2', '2013-05-29', '44', '54');
INSERT INTO `tbl_questions` VALUES ('A7', '12', 'units_2', '2013-05-29', '44', '55');
INSERT INTO `tbl_questions` VALUES ('A8', '7', 'units_2', '2013-05-29', '44', '56');
INSERT INTO `tbl_questions` VALUES ('A9', '15', 'units_2', '2013-05-29', '44', '57');
INSERT INTO `tbl_questions` VALUES ('A10', '15', 'units_2', '2013-05-29', '44', '58');
INSERT INTO `tbl_questions` VALUES ('A11', '13', 'units_2', '2013-05-29', '44', '59');
INSERT INTO `tbl_questions` VALUES ('A12', '12', 'units_2', '2013-05-29', '44', '60');
INSERT INTO `tbl_questions` VALUES ('A13', '32', 'units_2', '2013-05-29', '44', '61');
INSERT INTO `tbl_questions` VALUES ('B1', '5', 'units_2', '2013-05-29', '44', '62');
INSERT INTO `tbl_questions` VALUES ('B2', '3', 'units_2', '2013-05-29', '44', '63');
INSERT INTO `tbl_questions` VALUES ('B3', '16', 'units_2', '2013-05-29', '44', '64');
INSERT INTO `tbl_questions` VALUES ('B4', '4', 'units_2', '2013-05-29', '44', '65');
INSERT INTO `tbl_questions` VALUES ('B5', '18', 'units_2', '2013-05-29', '44', '66');
INSERT INTO `tbl_questions` VALUES ('B6', '9', 'units_2', '2013-05-29', '44', '67');
INSERT INTO `tbl_questions` VALUES ('B7', '5', 'units_2', '2013-05-29', '44', '68');
INSERT INTO `tbl_questions` VALUES ('B8', '6', 'units_2', '2013-05-29', '44', '69');
INSERT INTO `tbl_questions` VALUES ('B9', '8', 'units_2', '2013-05-29', '44', '70');
INSERT INTO `tbl_questions` VALUES ('B10', '2', 'units_2', '2013-05-29', '44', '71');
INSERT INTO `tbl_questions` VALUES ('B11', '13', 'units_2', '2013-05-29', '44', '72');
INSERT INTO `tbl_questions` VALUES ('B12', '11', 'units_2', '2013-05-29', '44', '73');
INSERT INTO `tbl_questions` VALUES ('B13', '10', 'units_2', '2013-05-29', '44', '74');
INSERT INTO `tbl_questions` VALUES ('C1', '37', 'units_2', '2013-05-29', '44', '75');
INSERT INTO `tbl_questions` VALUES ('C2', '1', 'units_2', '2013-05-29', '44', '76');
INSERT INTO `tbl_questions` VALUES ('C3', '5', 'units_2', '2013-05-29', '44', '77');
INSERT INTO `tbl_questions` VALUES ('C4', '10', 'units_2', '2013-05-29', '44', '78');
INSERT INTO `tbl_questions` VALUES ('C5', '6', 'units_2', '2013-05-29', '44', '79');
INSERT INTO `tbl_questions` VALUES ('C6', '19', 'units_2', '2013-05-29', '44', '80');
INSERT INTO `tbl_questions` VALUES ('C7', '22', 'units_2', '2013-05-29', '44', '81');
INSERT INTO `tbl_questions` VALUES ('C8', '12', 'units_2', '2013-05-29', '44', '82');
INSERT INTO `tbl_questions` VALUES ('C9', '19', 'units_2', '2013-05-29', '44', '83');
INSERT INTO `tbl_questions` VALUES ('C10', '9', 'units_2', '2013-05-29', '44', '84');
INSERT INTO `tbl_questions` VALUES ('C11', '13', 'units_2', '2013-05-29', '44', '85');
INSERT INTO `tbl_questions` VALUES ('C12', '21', 'units_2', '2013-05-29', '44', '86');
INSERT INTO `tbl_questions` VALUES ('C13', '2', 'units_2', '2013-05-29', '44', '87');
INSERT INTO `tbl_questions` VALUES ('D1', '1', 'units_2', '2013-05-29', '44', '88');
INSERT INTO `tbl_questions` VALUES ('D2', '31', 'units_2', '2013-05-29', '44', '89');
INSERT INTO `tbl_questions` VALUES ('D3', '14', 'units_2', '2013-05-29', '44', '90');
INSERT INTO `tbl_questions` VALUES ('D5', '16', 'units_2', '2013-05-29', '44', '91');
INSERT INTO `tbl_questions` VALUES ('D6', '0', 'units_2', '2013-05-29', '44', '92');
INSERT INTO `tbl_questions` VALUES ('D7', '5', 'units_2', '2013-05-29', '44', '93');
INSERT INTO `tbl_questions` VALUES ('D8', '20', 'units_2', '2013-05-29', '44', '94');
INSERT INTO `tbl_questions` VALUES ('D10', '16', 'units_2', '2013-05-29', '44', '95');
INSERT INTO `tbl_questions` VALUES ('D11', '3', 'units_2', '2013-05-29', '44', '96');