Simplest way to calculate the median with MySQL

cms9651 · May 27, 2013, 3:42pm

Simple way to calculate median with MySQL

What’s the simplest (and hopefully not too slow) way to calculate the median with MySQL?

I tried this query and I’ve this output:


mysql&gt; SELECT
	field1,
	field2,
	`names`,
	SUM(field2) / (
		Cast(
			(SELECT COUNT(*) FROM `tbl_11`) AS DECIMAL (18, 2)
		)
	) AS field2
FROM
	`tbl_11`
GROUP BY
	field1,
	`names`;
+--------+--------+-------+--------+
| field1 | field2 | names | field2 |
+--------+--------+-------+--------+
| A1     |     10 | L_1   | 0.0694 |
| A1     |     10 | L_2   | 0.0694 |
| A1     |     10 | L_3   | 0.0694 |
.........
.........
.........
+--------+--------+-------+--------+

144 rows in set

I expected this:


+--------+--------+-------+--------+
| field1 | field2 | names | field2 |
+--------+--------+-------+--------+
| A1     |     30 | L_1   | 10     |
+--------+--------+-------+--------+

where: 30/3 = 10 ===> 30 is the sum of single value ‘A1’ and 3 is the total of rows field1 equal to A1.

Can you help me?
Any help would be appreciated.


-- ----------------------------
-- Table structure for `tbl_11`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_11`;
CREATE TABLE `tbl_11` (
  `field1` varchar(255) DEFAULT NULL,
  `field2` int(10) DEFAULT NULL,
  `Names` varchar(255) DEFAULT NULL,
  `id` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=145 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of tbl_11
-- ----------------------------
INSERT INTO `tbl_11` VALUES ('A1', '10', 'L_1', '1');
INSERT INTO `tbl_11` VALUES ('B1', '20', 'L_1', '2');
INSERT INTO `tbl_11` VALUES ('C1', '30', 'L_1', '3');
INSERT INTO `tbl_11` VALUES ('D1', '40', 'L_1', '4');
INSERT INTO `tbl_11` VALUES ('A2', '5', 'L_1', '5');
INSERT INTO `tbl_11` VALUES ('B2', '6', 'L_1', '6');
INSERT INTO `tbl_11` VALUES ('C2', '7', 'L_1', '7');
INSERT INTO `tbl_11` VALUES ('D2', '8', 'L_1', '8');
INSERT INTO `tbl_11` VALUES ('A3', '9', 'L_1', '9');
INSERT INTO `tbl_11` VALUES ('B3', '0', 'L_1', '10');
INSERT INTO `tbl_11` VALUES ('C3', '1', 'L_1', '11');
INSERT INTO `tbl_11` VALUES ('D3', '3', 'L_1', '12');
INSERT INTO `tbl_11` VALUES ('A4', '5', 'L_1', '13');
INSERT INTO `tbl_11` VALUES ('B4', '5', 'L_1', '14');
INSERT INTO `tbl_11` VALUES ('C4', '5', 'L_1', '15');
INSERT INTO `tbl_11` VALUES ('A5', '5', 'L_1', '16');
INSERT INTO `tbl_11` VALUES ('B5', '5', 'L_1', '17');
INSERT INTO `tbl_11` VALUES ('C5', '5', 'L_1', '18');
INSERT INTO `tbl_11` VALUES ('D5', '5', 'L_1', '19');
INSERT INTO `tbl_11` VALUES ('A6', '1', 'L_1', '20');
INSERT INTO `tbl_11` VALUES ('B6', '4', 'L_1', '21');
INSERT INTO `tbl_11` VALUES ('C6', '4', 'L_1', '22');
INSERT INTO `tbl_11` VALUES ('D6', '5', 'L_1', '23');
INSERT INTO `tbl_11` VALUES ('A7', '5', 'L_1', '24');
INSERT INTO `tbl_11` VALUES ('B7', '5', 'L_1', '25');
INSERT INTO `tbl_11` VALUES ('C7', '5', 'L_1', '26');
INSERT INTO `tbl_11` VALUES ('D7', '5', 'L_1', '27');
INSERT INTO `tbl_11` VALUES ('A8', '5', 'L_1', '28');
INSERT INTO `tbl_11` VALUES ('B8', '5', 'L_1', '29');
INSERT INTO `tbl_11` VALUES ('C8', '2', 'L_1', '30');
INSERT INTO `tbl_11` VALUES ('D8', '5', 'L_1', '31');
INSERT INTO `tbl_11` VALUES ('A9', '5', 'L_1', '32');
INSERT INTO `tbl_11` VALUES ('B9', '5', 'L_1', '33');
INSERT INTO `tbl_11` VALUES ('C9', '5', 'L_1', '34');
INSERT INTO `tbl_11` VALUES ('A10', '5', 'L_1', '35');
INSERT INTO `tbl_11` VALUES ('B10', '5', 'L_1', '36');
INSERT INTO `tbl_11` VALUES ('C10', '5', 'L_1', '37');
INSERT INTO `tbl_11` VALUES ('D10', '5', 'L_1', '38');
INSERT INTO `tbl_11` VALUES ('A11', '5', 'L_1', '39');
INSERT INTO `tbl_11` VALUES ('B11', '3', 'L_1', '40');
INSERT INTO `tbl_11` VALUES ('C11', '6', 'L_1', '41');
INSERT INTO `tbl_11` VALUES ('D11', '2', 'L_1', '42');
INSERT INTO `tbl_11` VALUES ('A12', '5', 'L_1', '43');
INSERT INTO `tbl_11` VALUES ('B12', '5', 'L_1', '44');
INSERT INTO `tbl_11` VALUES ('C12', '5', 'L_1', '45');
INSERT INTO `tbl_11` VALUES ('A13', '5', 'L_1', '46');
INSERT INTO `tbl_11` VALUES ('B13', '5', 'L_1', '47');
INSERT INTO `tbl_11` VALUES ('C13', '5', 'L_1', '48');
INSERT INTO `tbl_11` VALUES ('A1', '10', 'L_2', '49');
INSERT INTO `tbl_11` VALUES ('B1', '20', 'L_2', '50');
INSERT INTO `tbl_11` VALUES ('C1', '30', 'L_2', '51');
INSERT INTO `tbl_11` VALUES ('D1', '40', 'L_2', '52');
INSERT INTO `tbl_11` VALUES ('A2', '5', 'L_2', '53');
INSERT INTO `tbl_11` VALUES ('B2', '6', 'L_2', '54');
INSERT INTO `tbl_11` VALUES ('C2', '7', 'L_2', '55');
INSERT INTO `tbl_11` VALUES ('D2', '8', 'L_2', '56');
INSERT INTO `tbl_11` VALUES ('A3', '9', 'L_2', '57');
INSERT INTO `tbl_11` VALUES ('B3', '0', 'L_2', '58');
INSERT INTO `tbl_11` VALUES ('C3', '1', 'L_2', '59');
INSERT INTO `tbl_11` VALUES ('D3', '3', 'L_2', '60');
INSERT INTO `tbl_11` VALUES ('A4', '5', 'L_2', '61');
INSERT INTO `tbl_11` VALUES ('B4', '5', 'L_2', '62');
INSERT INTO `tbl_11` VALUES ('C4', '5', 'L_2', '63');
INSERT INTO `tbl_11` VALUES ('A5', '5', 'L_2', '64');
INSERT INTO `tbl_11` VALUES ('B5', '5', 'L_2', '65');
INSERT INTO `tbl_11` VALUES ('C5', '5', 'L_2', '66');
INSERT INTO `tbl_11` VALUES ('D5', '5', 'L_2', '67');
INSERT INTO `tbl_11` VALUES ('A6', '1', 'L_2', '68');
INSERT INTO `tbl_11` VALUES ('B6', '4', 'L_2', '69');
INSERT INTO `tbl_11` VALUES ('C6', '4', 'L_2', '70');
INSERT INTO `tbl_11` VALUES ('D6', '5', 'L_2', '71');
INSERT INTO `tbl_11` VALUES ('A7', '5', 'L_2', '72');
INSERT INTO `tbl_11` VALUES ('B7', '5', 'L_2', '73');
INSERT INTO `tbl_11` VALUES ('C7', '5', 'L_2', '74');
INSERT INTO `tbl_11` VALUES ('D7', '5', 'L_2', '75');
INSERT INTO `tbl_11` VALUES ('A8', '5', 'L_2', '76');
INSERT INTO `tbl_11` VALUES ('B8', '5', 'L_2', '77');
INSERT INTO `tbl_11` VALUES ('C8', '2', 'L_2', '78');
INSERT INTO `tbl_11` VALUES ('D8', '5', 'L_2', '79');
INSERT INTO `tbl_11` VALUES ('A9', '5', 'L_2', '80');
INSERT INTO `tbl_11` VALUES ('B9', '5', 'L_2', '81');
INSERT INTO `tbl_11` VALUES ('C9', '5', 'L_2', '82');
INSERT INTO `tbl_11` VALUES ('A10', '5', 'L_2', '83');
INSERT INTO `tbl_11` VALUES ('B10', '5', 'L_2', '84');
INSERT INTO `tbl_11` VALUES ('C10', '5', 'L_2', '85');
INSERT INTO `tbl_11` VALUES ('D10', '5', 'L_2', '86');
INSERT INTO `tbl_11` VALUES ('A11', '5', 'L_2', '87');
INSERT INTO `tbl_11` VALUES ('B11', '3', 'L_2', '88');
INSERT INTO `tbl_11` VALUES ('C11', '6', 'L_2', '89');
INSERT INTO `tbl_11` VALUES ('D11', '2', 'L_2', '90');
INSERT INTO `tbl_11` VALUES ('A12', '5', 'L_2', '91');
INSERT INTO `tbl_11` VALUES ('B12', '5', 'L_2', '92');
INSERT INTO `tbl_11` VALUES ('C12', '5', 'L_2', '93');
INSERT INTO `tbl_11` VALUES ('A13', '5', 'L_2', '94');
INSERT INTO `tbl_11` VALUES ('B13', '5', 'L_2', '95');
INSERT INTO `tbl_11` VALUES ('C13', '5', 'L_2', '96');
INSERT INTO `tbl_11` VALUES ('A1', '10', 'L_3', '97');
INSERT INTO `tbl_11` VALUES ('B1', '20', 'L_3', '98');
INSERT INTO `tbl_11` VALUES ('C1', '30', 'L_3', '99');
INSERT INTO `tbl_11` VALUES ('D1', '40', 'L_3', '100');
INSERT INTO `tbl_11` VALUES ('A2', '5', 'L_3', '101');
INSERT INTO `tbl_11` VALUES ('B2', '6', 'L_3', '102');
INSERT INTO `tbl_11` VALUES ('C2', '7', 'L_3', '103');
INSERT INTO `tbl_11` VALUES ('D2', '8', 'L_3', '104');
INSERT INTO `tbl_11` VALUES ('A3', '9', 'L_3', '105');
INSERT INTO `tbl_11` VALUES ('B3', '0', 'L_3', '106');
INSERT INTO `tbl_11` VALUES ('C3', '1', 'L_3', '107');
INSERT INTO `tbl_11` VALUES ('D3', '3', 'L_3', '108');
INSERT INTO `tbl_11` VALUES ('A4', '5', 'L_3', '109');
INSERT INTO `tbl_11` VALUES ('B4', '5', 'L_3', '110');
INSERT INTO `tbl_11` VALUES ('C4', '5', 'L_3', '111');
INSERT INTO `tbl_11` VALUES ('A5', '5', 'L_3', '112');
INSERT INTO `tbl_11` VALUES ('B5', '5', 'L_3', '113');
INSERT INTO `tbl_11` VALUES ('C5', '5', 'L_3', '114');
INSERT INTO `tbl_11` VALUES ('D5', '5', 'L_3', '115');
INSERT INTO `tbl_11` VALUES ('A6', '1', 'L_3', '116');
INSERT INTO `tbl_11` VALUES ('B6', '4', 'L_3', '117');
INSERT INTO `tbl_11` VALUES ('C6', '4', 'L_3', '118');
INSERT INTO `tbl_11` VALUES ('D6', '5', 'L_3', '119');
INSERT INTO `tbl_11` VALUES ('A7', '5', 'L_3', '120');
INSERT INTO `tbl_11` VALUES ('B7', '5', 'L_3', '121');
INSERT INTO `tbl_11` VALUES ('C7', '5', 'L_3', '122');
INSERT INTO `tbl_11` VALUES ('D7', '5', 'L_3', '123');
INSERT INTO `tbl_11` VALUES ('A8', '5', 'L_3', '124');
INSERT INTO `tbl_11` VALUES ('B8', '5', 'L_3', '125');
INSERT INTO `tbl_11` VALUES ('C8', '2', 'L_3', '126');
INSERT INTO `tbl_11` VALUES ('D8', '5', 'L_3', '127');
INSERT INTO `tbl_11` VALUES ('A9', '5', 'L_3', '128');
INSERT INTO `tbl_11` VALUES ('B9', '5', 'L_3', '129');
INSERT INTO `tbl_11` VALUES ('C9', '5', 'L_3', '130');
INSERT INTO `tbl_11` VALUES ('A10', '5', 'L_3', '131');
INSERT INTO `tbl_11` VALUES ('B10', '5', 'L_3', '132');
INSERT INTO `tbl_11` VALUES ('C10', '5', 'L_3', '133');
INSERT INTO `tbl_11` VALUES ('D10', '5', 'L_3', '134');
INSERT INTO `tbl_11` VALUES ('A11', '5', 'L_3', '135');
INSERT INTO `tbl_11` VALUES ('B11', '3', 'L_3', '136');
INSERT INTO `tbl_11` VALUES ('C11', '6', 'L_3', '137');
INSERT INTO `tbl_11` VALUES ('D11', '2', 'L_3', '138');
INSERT INTO `tbl_11` VALUES ('A12', '5', 'L_3', '139');
INSERT INTO `tbl_11` VALUES ('B12', '5', 'L_3', '140');
INSERT INTO `tbl_11` VALUES ('C12', '5', 'L_3', '141');
INSERT INTO `tbl_11` VALUES ('A13', '5', 'L_3', '142');
INSERT INTO `tbl_11` VALUES ('B13', '5', 'L_3', '143');
INSERT INTO `tbl_11` VALUES ('C13', '5', 'L_3', '144');

DarthGuido · May 27, 2013, 4:07pm

If you group by ‘names’, and the ‘names’ column contains three different values, why would you expect to have only 1 of them returned?

cms9651 · May 27, 2013, 4:17pm

Hi Guido and thank you for reply.
I understand your suggestion adn tried this, but the output is not what I expect …

mysql&gt; SELECT
	field1,
	field2,
	`names`,
	SUM(field2) / (
		Cast(
			(SELECT COUNT(*) FROM `tbl_11`) AS DECIMAL (18, 2)
		)
	) AS field2
FROM
	`tbl_11`
GROUP BY
	field1;
+--------+--------+-------+--------+
| field1 | field2 | names | field2 |
+--------+--------+-------+--------+
| A1     |     10 | L_1   | 0.2083 |
.........
.........
.........
+--------+--------+-------+--------+
48 rows in set

DarthGuido · May 27, 2013, 4:20pm

if you want to sum the values of field2, do so
if you want to divide by the number of rows that have the value ‘L_1’ in the ‘names’ column, then extract that number of rows, and not all rows in the table

cms9651 · May 27, 2013, 4:22pm

I don’t understand…

[SQL] SELECT
	field1,
	field2,
	`names`,
	SUM(field2) / (
		Cast(
			(SELECT COUNT(*) FROM `tbl_11` GROUP BY field1) AS DECIMAL (18, 2)
		)
	) AS field2
FROM
	`tbl_11`
GROUP BY
	field1;
[Err] 1242 - Subquery returns more than 1 row

cms9651 · May 27, 2013, 5:29pm

Solved, thank you:

mysql&gt; SELECT
	field1,
	field2,
	SUM(field2) / COUNT(*)
FROM
	`tbl_11`
GROUP BY
	field1;
+--------+--------+------------------------+
| field1 | field2 | SUM(field2) / COUNT(*) |
+--------+--------+------------------------+
| A1     |     10 | 10.0000                |
| A10    |      5 | 5.0000                 |
| A11    |      5 | 5.0000                 |
| A12    |      5 | 5.0000                 |
| A13    |      5 | 5.0000                 |
......
......
+--------+--------+------------------------+
48 rows in set

rcashell · May 27, 2013, 6:08pm

Have you looked at the AVG function?

cms9651 · May 27, 2013, 6:10pm

Not Sir, not looked… do you any examples?

rcashell · May 27, 2013, 6:19pm

Sure

SELECT field1, AVG(field2)
FROM tbl_11
GROUP BY field1;

cms9651 · May 27, 2013, 6:53pm

thanks so much!

r937 · May 27, 2013, 11:18pm

i would just like to point out that AVG() produces the mean, not the median

cms9651 · May 29, 2013, 6:51pm

You have right… . it’s mean and not median …

r937 · May 29, 2013, 8:04pm

so, which did you want?

cms9651 · June 1, 2013, 6:50am

initially no … but I realized that the mean is more representative in my case than the median …
thank you very much.