# Simplest way to calculate the median with MySQL

• May 27, 2013, 08:42
cms9651
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');```
• May 27, 2013, 09:07
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?
• May 27, 2013, 09:17
cms9651
Quote:

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:

``` 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; +--------+--------+-------+--------+ | field1 | field2 | names | field2 | +--------+--------+-------+--------+ | A1     |     10 | L_1   | 0.2083 | ......... ......... ......... +--------+--------+-------+--------+ 48 rows in set  ```
• May 27, 2013, 09:20
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
• May 27, 2013, 09:22
cms9651
Quote:

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:

``` [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  ```
• May 27, 2013, 10:29
cms9651
Solved, thank you: :)
PHP Code:

``` mysql> 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  ```
• May 27, 2013, 11:08
rcashell
Have you looked at the AVG function?
• May 27, 2013, 11:10
cms9651
Quote:

Originally Posted by rcashell
Have you looked at the AVG function?

Not Sir, not looked... do you any examples?
• May 27, 2013, 11:19
rcashell
Sure

SELECT field1, AVG(field2)
FROM tbl_11
GROUP BY field1;
• May 27, 2013, 11:53
cms9651
Quote:

Originally Posted by rcashell
Sure

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

thanks so much!
• May 27, 2013, 16:18
r937
Quote:

Originally Posted by cms9651
thanks so much!

i would just like to point out that AVG() produces the mean, not the median
• May 29, 2013, 11:51
cms9651
Quote:

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 ...
• May 29, 2013, 13:04
r937
Quote:

Originally Posted by cms9651
You have right.. . it's mean and not median ...

so, which did you want?
• May 31, 2013, 23:50
cms9651
Quote:

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.