Postgres: Get aggregation query based on multiple values in a column

Question

I have following database table:

id, owner_id, partner_id, level
1, 121, 234, gold
2, 121, 342, silver
3, 121, 423, silver
4, 233, 333, silver
5, 233, 434, gold

I am trying to get result in following format:

owner_id, number_of_partners, total_gold, total_silver, total_bronze

I am not sure if this is possible to do, I so far I started with the basic group by query where I get the total partners per owner.

SELECT owner_id, count(partner_id) FROM owner_partner GROUP BY owner_id

How can I extend this to get my results?

Gordon Linoff · Accepted Answer · 2014-08-04 20:29:29Z

1

Just use conditional aggregation:

SELECT owner_id, count(partner_id),
       sum(case when level = 'gold' then 1 else 0 end) as gold,
       sum(case when level = 'silver' then 1 else 0 end) as silver,
       sum(case when level = 'bronze' then 1 else 0 end) as bronze
FROM owner_partner
GROUP BY owner_id;

answered Aug 4, 2014 at 20:29

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Comments

Erwin Brandstetter · Accepted Answer · 2014-08-04 20:51:29Z

1

I suggest shorter syntax (about same performance as CASE statements):

SELECT owner_id
     , count(*) AS total
     , count(level = 'gold' OR NULL) AS gold
     , count(level = 'silver' OR NULL) AS silver
     , count(level = 'bronze' OR NULL) AS bronze
FROM   tbl
GROUP  BY 1;

answered Aug 4, 2014 at 20:51

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

Postgres: Get aggregation query based on multiple values in a column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related