locked
Simple way to calculate median with MySQL RRS feed

  • Question

  • User1872364149 posted

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






    Monday, May 27, 2013 1:14 PM

Answers

  • User-837620913 posted

    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;



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 28, 2013 5:35 AM

All replies

  • User-837620913 posted

    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;



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 28, 2013 5:35 AM
  • User1872364149 posted

    thank you.

    Wednesday, May 29, 2013 4:42 AM