Results 1 to 3 of 3

Thread: Sum of rows

  1. #1
    Join Date
    Aug 2012
    Posts
    30

    Unanswered: Sum of rows

    Hi all, hope in your help.

    This is my mysql table:
    Code:
    +----+--------+--------+
    | id | field1 | field2 |
    +----+--------+--------+
    |  1 | A1     | 7      |
    |  2 | B1     | 9      |
    |  3 | C1     | 0      |
    |  4 | D1     | 3      |
    |  5 | A2     | 5      |
    |  6 | B2     | 6      |
    |  7 | C2     | 7      |
    |  8 | D2     | 8      |
    +----+--------+--------+
    I need this output:
    Code:
    +--------+--------------------+
    | field1 | field2             |
    +--------+--------------------+
    | A2     | 19.230769230769200 |
    +--------+--------------------+
    | B2     | 23,076923076923100 |
    +--------+--------------------+
    | C2     | 26,923076923076900 |
    +--------+--------------------+
    | D2     | 30,769230769230800 |
    +--------+--------------------+
    and tried this query where calculate the value of single field1 (5,6,7,8) divided by the sum of field2 equal to A1, B2, C2 and D2 (26) :
    Code:
    A = 5/26 * 100 = 19
    B = 6/26 * 100 = 23
    C = 7/26 * 100 = 26
    D = 8/26 * 100 = 30:
    
    SELECT
    	field1,
    	field2/Sum(field2)*100 as field2
    FROM
    	`tbl_t`
    WHERE
    	1
    AND field1 IN ('A2', 'B2', 'C2', 'D2');
    
    +--------+--------------------+
    | field1 | field2             |
    +--------+--------------------+
    | A2     | 19.230769230769234 |
    +--------+--------------------+
    1 row in set
    But the ouput is not what I want, can you help me?
    Thank you
    Any help would be greatly appreciated.
    Last edited by cms9651; 05-25-13 at 12:36.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    So you are trying to get a percentage of the a total based on a filtered set of data.

    Code:
    SELECT field1, sum(field2)/totalsum
    FROM table1, (SELECT sum(field2) as totalsum FROM table1 WHERE field1 IN ('A2', 'B2', 'C2', 'D2')) as total
    WHERE field1 IN ('A2', 'B2', 'C2', 'D2');
    This is untested but gives you an idea of what to do.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Aug 2012
    Posts
    30
    thank so 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
  •