CODEWITHSUNDEEP
postgresql
Wells
Wells

Reputation: 10969

postgres: unnesting an array and counting totals

I have data that looks like this:

winning_pid  losing_pid  pair  wins
          1           2   1,2     7
          2           1   1,2     3
          3           4   3,4     2

And I want a result that looks like this:

pid  opp_pid  total  wins  losses
  1        2     10     7      3
  2        1     10     3      7
  3        4      2     2      0
  4        3      2     0      2

It's basically: matchup totals per pid value and an opponent pid value, with a total between them and a # of wins and losses. As you can see, you have situations where a losing_pid value never shows in the winning_pid column as that pid value has no wins, but these need to be in the totals table.

Is there a quick solution using UNNEST() on pair? I can't do something like this:

SELECT DISTINCT ON (pair) UNNEST(pair) as pid, COUNT(*) FILTER ( WHERE pid = winning_pid ) AS wins because it doesn't recognize pid in the FILTER clause.

I'm also thinking UNNEST() isn't what I want as I want a results table that has both pid values, versus just the one.

Thanks!

Upvotes: 1

Views: 56

Answers (1)

klin
klin

Reputation: 121594

Use union for switched results:

select
    pid, 
    opp_pid, 
    sum(wins + loses) total,
    sum(wins) wins, 
    sum(loses) loses
from (
    select winning_pid pid, losing_pid opp_pid, wins, 0 loses
    from example
    union
    select losing_pid, winning_pid, 0, wins
    from example
) s
group by 1, 2
order by 1, 2;

Test it in rextester.

Upvotes: 1

Related Questions