0

I have a Users Table

it has id, username, A, and B as my columns.

I also have a Votes Table.

a user can vote on A, and B.

it has id, user_id foreign key, A_id foreign key, and B_id foreign key.

I would like to do a query to Users and have the tally of votes for A and votes for B included.

so lets say I find a User with the id of 1,

How would I get something like

{
        "id": 1,
        "display_name": "test",
        "A" : 32,
        "B" : 132
}

Assuming there are 32 rows in the Votes table and 132 rows for B in the Votes table.

Billal Begueradj
  • 20,717
  • 43
  • 112
  • 130
Amy Coin
  • 141
  • 1
  • 1
  • 4

1 Answers1

0

simplified table:

t=# select* from users;
 i | t
---+---
 1 | A
 1 | B
 1 | B
 2 | A
 2 | A
 2 | B
(6 rows)

query:

t=# select distinct
i
, sum(case when t='A' then count(1) filter (where t='A') else 0 end) over (partition by i) a
, sum(case when t='B' then count(1) filter (where t='B') else 0 end ) over (partition by i)b
from users
group by i,t
order by i;
 i | a | b
---+---+---
 1 | 1 | 2
 2 | 2 | 1
(2 rows)
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132