Thread: Sum of rows

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    8
    Rep Power
    0

    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.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,170
    Rep Power
    4274
    Code:
    SELECT field1
         , field2 / 
           ( SELECT SUM(field2) * 100 
               FROM tbl_t
              WHERE field1 IN ('A2', 'B2', 'C2', 'D2') ) AS field2
      FROM tbl_t                             
     WHERE field1 IN ('A2', 'B2', 'C2', 'D2')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    8
    Rep Power
    0
    thanks so much!

IMN logo majestic logo threadwatch logo seochat tools logo