Last post May 29, 2013 04:42 AM by cms9651
Member
390 Points
964 Posts
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');
All-Star
66173 Points
10248 Posts
MVP
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;
May 29, 2013 04:42 AM|cms9651|LINK
thank you.
Member
390 Points
964 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:
I expected this:
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.
Cheers,
Chevy Mark Sunderland
All-Star
66173 Points
10248 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:
Darrell Norton's Blog
Please click "Mark as Answer" if this helped you.
Member
390 Points
964 Posts
Re: Simple way to calculate median with MySQL
May 29, 2013 04:42 AM|cms9651|LINK
thank you.
Cheers,
Chevy Mark Sunderland