PostgreSQL – Build Select Query with Aggregated Values from Different Table

aggregate-filterpivotpostgresqlquery

I have two tables in Postgres: polls and votes.

The first one polls is designed to contain the poll-related data. Every poll is aimed to have only two possible responses — option_a and option_b

+----+---------+----------+----------+------------+
| id | author  | option_a | option_b | created_at |
+----+---------+----------+----------+------------+
| 1  | user_01 | apple    | banana   | 2020/08/15 |
+----+---------+----------+----------+------------+
| 2  | user_02 | tea      | coffee   | 2020/08/16 |
+----+---------+----------+----------+------------+

The second one votes is holding data about the votes:

+---------+---------+--------+------------+
| poll_id | voter   | option | voted_at   |
+---------+---------+--------+------------+
| 1       | user_01 | apple  | 2020/08/15 |
+---------+---------+--------+------------+
| 1       | user_02 | banana | 2020/08/15 |
+---------+---------+--------+------------+
| 1       | user_03 | banana | 2020/08/15 |
+---------+---------+--------+------------+
| 1       | user_04 | apple  | 2020/08/15 |
+---------+---------+--------+------------+
| 1       | user_05 | apple  | 2020/08/15 |
+---------+---------+--------+------------+
| 2       | user_01 | tea    | 2020/08/16 |
+---------+---------+--------+------------+
| 2       | user_08 | coffee | 2020/08/16 |
+---------+---------+--------+------------+

What I'm trying to do is to select polls with votes count.
E.g. for selecting the data for poll with id = 1, I expect to get:

+---------+----------+----------+------------+
| poll_id | option_a | option_b | created_at |
+---------+----------+----------+------------+
| 1       | 3        | 2        | 2020/08/15 |
+---------+----------+----------+------------+

How to compose such a query?

Best Answer

You can use filtered aggregation for this:

select v.poll_id, 
       count(*) filter (where v.option = p.option_a) as option_a, 
       count(*) filter (where v.option = p.option_b) as option_b,
       max(p.created_at) as created_at
from votes v 
  join polls p on p.id = v.poll_id
group by v.poll_id
order by v.poll_id;

The max(p.created_at) is necessary to make the group by happy.

Online example

Related Question