Blog

Simple way to calculate median with MySQL [Answered]RSS

2 replies

Last post May 29, 2013 04:42 AM by cms9651

‹ Previous Thread|Next Thread ›

Shortcuts

cms9651

Member

384 Points

952 Posts

Simple way to calculate median with MySQL

May 27, 2013 01:14 PM|cms9651|LINK

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> 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');

I would greatly appreciate any help you can give me in working this problem.

Cheers,
Chevy Mark Sunderland

- Reply
DarrellNorto...

All-Star

66183 Points

10249 Posts

MVP
Re: Simple way to calculate median with MySQL

May 28, 2013 05:35 AM|DarrellNorton|LINK
So what you are asking for is the AVERAGE, not the MEDIAN. The average is values / count of values.

MySQL has an average function built-in:
```
SELECT field1, AVG(field2)
FROM table_name
GROUP BY field1;
```
Darrell Norton, MVP
Darrell Norton's Blog
Please click "Mark as Answer" if this helped you.
- Reply
cms9651

Member

384 Points

952 Posts

Re: Simple way to calculate median with MySQL

May 29, 2013 04:42 AM|cms9651|LINK

thank you.

I would greatly appreciate any help you can give me in working this problem.

Cheers,
Chevy Mark Sunderland

‹ Previous Thread|Next Thread ›

Simple way to calculate median with MySQL [Answered]RSS

2 replies

Shortcuts

cms9651

Simple way to calculate median with MySQL

DarrellNorto...

Re: Simple way to calculate median with MySQL

cms9651

Re: Simple way to calculate median with MySQL