# Thread: Simplest way to calculate the median with MySQL

1. ## Simplest way to calculate the median with MySQL

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:
Code:
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:
Code:
+--------+--------+-------+--------+
| 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.
Code:
-- ----------------------------
-- 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');

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

3. Originally Posted by guido2004
If you group by 'names', and the 'names' column contains three different values, why would you expect to have only 1 of them returned?
Hi Guido and thank you for reply.
I understand your suggestion adn tried this, but the output is not what I expect ...
PHP Code:
mysqlSELECT
field1
,

field2,
`
names`,

SUM(field2) / (

Cast(
(
SELECT COUNT(*) FROM `tbl_11`) AS DECIMAL (182)
)
) AS
field2
FROM

`tbl_11`
GROUP BY
field1
;
+--------+--------+-------+--------+
field1 field2 names field2 |
+--------+--------+-------+--------+
A1     |     10 L_1   0.2083 |
.........
.........
.........
+--------+--------+-------+--------+
48 rows in set

4. 1) if you want to sum the values of field2, do so
2) 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

5. Originally Posted by guido2004
1) if you want to sum the values of field2, do so
2) 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
I don't understand...
PHP Code:
[SQLSELECT
field1
,

field2,
`
names`,

SUM(field2) / (

Cast(
(
SELECT COUNT(*) FROM `tbl_11GROUP BY field1) AS DECIMAL (182)
)
) AS
field2
FROM

`tbl_11`
GROUP BY
field1
;
[
Err1242 Subquery returns more than 1 row

6. Solved, thank you:
PHP Code:
mysqlSELECT
field1
,

field2,

SUM(field2) / COUNT(*)
FROM

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

7. Have you looked at the AVG function?

8. Originally Posted by rcashell
Have you looked at the AVG function?
Not Sir, not looked... do you any examples?

9. Sure

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

10. Originally Posted by rcashell
Sure

SELECT field1, AVG(field2)
FROM tbl_11
GROUP BY field1;
thanks so much!

11. Originally Posted by cms9651
thanks so much!
i would just like to point out that AVG() produces the mean, not the median

12. Originally Posted by r937
i would just like to point out that AVG() produces the mean, not the median
You have right.. . it's mean and not median ...

13. Originally Posted by cms9651
You have right.. . it's mean and not median ...
so, which did you want?

14. Originally Posted by r937
so, which did you want?
initially no ... but I realized that the mean is more representative in my case than the median ...
thank you very much.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•