how to accumulate values in different columns ?

jfps · #1 (**permalink**) 04-17-07, 05:42

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 ?

ivon · #2 (**permalink**) 04-17-07, 06:05

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.

jfps · #3 (**permalink**) 04-17-07, 06:13

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 !

Internet Services

Web Development

Digital Marketing

Consumer Tech