Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2007
    Posts
    2

    Unanswered: how to accumulate values in different columns ?

    Hi,

    I have a table similar to this :

    Code:
     id |  name  | res_id 
    ----+--------+--------
      1 | foo    |      1
      2 | bar    |      1
      3 | foo    |      1
      4 | test   |      2
      5 | wheyho |      3
      6 | ding   |      2
      7 | dong   |      3
      8 | foo    |      2
      9 | foo    |      3
     10 | bar    |      1
     11 | bar    |      2
     12 | bar    |      2
     13 | wheyho |      3
    Now, I am looking for a SELECT statement that counts the result_id depending on its value ( 1,2 or 3 in this example ) and grouped by name. To make it a bit more clear I am looking for this result (with result_id 1 evaluates to 'pass', 2 to 'fail' and 3 to 'unknown' ):

    Code:
    name     | pass | fail | unknown
    ---------+------+------+----------
    foo      | 2    | 1    | 1
    bar      | 2    | 2    | 0
    test     | 0    | 1    | 0
    wheyho   | 0    | 0    | 2
    ding     | 0    | 1    | 0
    dong     | 0    | 0    | 1
    I can get single results when doing this staements :

    Code:
    SELECT name, count(result_id) AS pass FROM t1 GROUP BY name, res_id HAVING res_id = 1;
    
     name | pass 
    ------+------
     foo  |    2
     bar  |    2
    
    SELECT name, count(result_id) AS fail FROM t1 GROUP BY name, res_id HAVING res_id = 2;
    
     name | fail 
    ------+------
     foo  |    1
     bar  |    2
     ding |    1
     test |    1
    
    SELECT name, count(result_id) AS unknown FROM t1 GROUP BY name, res_id HAVING res_id = 3;
    
      name  | unknown 
    --------+---------
     foo    |       1
     wheyho |       2
     dong   |       1
    Is there any way to bring these single results together with one SELECT statements ?

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    With SUMs and CASEs:

    SELECT
    name,
    SUM(CASE WHEN res_id = 1 THEN 1 ELSE 0 END) as pass
    SUM(CASE WHEN res_id = 2 THEN 1 ELSE 0 END) as fail
    SUM(CASE WHEN res_id = 3 THEN 1 ELSE 0 END) as unknown
    FROM t1
    GROUP BY name

    Untested, syntax may be off.

  3. #3
    Join Date
    Apr 2007
    Posts
    2
    Wow, this was a fast reply! And, what is more, a very good one. It works just the way I was looking for !

    thanks a lot !

Posting Permissions

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