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
– Craig Ringer, Commented Oct 17, 2012 at 22:22

John Woo · Accepted Answer · 2012-10-17 17:18:39Z

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, 2012 at 17:18

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rizwan Patel Over a year ago

how to do dynamic pivot without crosstab !

Collectives™ on Stack Overflow

Postgres simple 'pivot' table

1 Answer 1

SQLFiddle Demo

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related