SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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');
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,408
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    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. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    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 
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,408
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    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. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    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 
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  6. #6
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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 
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  7. #7
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Have you looked at the AVG function?

  8. #8
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by rcashell View Post
    Have you looked at the AVG function?
    Not Sir, not looked... do you any examples?
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  9. #9
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Sure

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

  10. #10
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by rcashell View Post
    Sure

    SELECT field1, AVG(field2)
    FROM tbl_11
    GROUP BY field1;
    thanks so much!
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cms9651 View Post
    thanks so much!
    i would just like to point out that AVG() produces the mean, not the median
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i would just like to point out that AVG() produces the mean, not the median
    You have right.. . it's mean and not median ...
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cms9651 View Post
    You have right.. . it's mean and not median ...
    so, which did you want?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


Bookmarks

Posting Permissions

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