how to accumulate values in different columns ?

Hi,

I have a table similar to this :

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' ):

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 :

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 ?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.|||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 !