To start at my original problem that is too specific for a question: I have a table of matches in the format:
ID|Player1|Player2|P1Score|P2Score
--+-------+-------+-------+-------
1| 71| 83| 2| 0
2| 73| 71| 1| 1
3| 71| 65| 2| 0
4| 65| 83| 0| 2
Where
I need to get a table in the format:
Player|Wins|Draws|Losses
------+----+-----+------
71| 2| 1| 0
73| 0| 1| 0
83| 1| 0| 1
65| 0| 0| 1
And I don't have much of an idea how to go about it.
The closest I got was to
SELECT Player1, COUNT(P1Score) FROM matches WHERE P1Score = 2 GROUP BY Player1
and repeat that for draws and losses, then join the tables, repeat for P2, and the sum the values together, but it seems JOIN isn't quite the command I need.
2 is a win, 1 is a tie, and 0 is a loss
Given each of these conditions, we can use case expressions to calculate the corresponding columns by score. Using a union all to unpivot the data, and then sum() to aggregate it by Player:
select
Player
, sum(Wins) as Wins
, sum(Draws) as Draws
, sum(Losses) as Losses
from (
select
Player1 as Player
, case when p1score = 2 then 1 else 0 end as Wins
, case when p1score = 1 then 1 else 0 end as Draws
, case when p1score = 0 then 1 else 0 end as Losses
from t
union all
select
Player2 as Player
, case when p2score = 2 then 1 else 0 end as Wins
, case when p2score = 1 then 1 else 0 end as Draws
, case when p2score = 0 then 1 else 0 end as Losses
from t
) as s
group by Player