Postgres simple 'pivot' table

Question

If I have a table of data as such

name   |   type   |  count

test   | blue     |  6
test2  | red      |  3
test   | red      |  4

How can I query it such that I get a table:

name  |  num_red  | num_blue

test  | 4         | 6
test2 | 3         | 0

I can of course select count(*) where type=blue etc but I can't think of how to count multiple types within one query like this.

Thanks!

Answer 1

You can use CASE in you select clause.

SELECT  name,
        SUM(CASE WHEN type = 'red' THEN "count" ELSE 0 END) numred,
        SUM(CASE WHEN type = 'blue' THEN "count" ELSE 0 END) numblue
FROM tableName
GROUP BY name

SQLFiddle Demo