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!

This is fairly basic SQL, so rather than using John's answer verbatim I strongly recommend you read the PostgreSQL tutorial and user manual so you understand *why* it works and how. — Craig Ringer, Oct 17 '12 at 22:22

score 6 · Accepted Answer · answered Oct 17 '12 at 17:18

6

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

answered Oct 17 '12 at 17:18

John Woo

258,903
69
498
492

how to do dynamic pivot without crosstab ! – Rizwan Patel May 13 '17 at 19:37

Postgres simple 'pivot' table

1 Answers1

SQLFiddle Demo

Linked