MYSQL join one colum from one table to two count() in another

user_12940603 0
Viewed 46

I have a table that stores a ranking for an online game. This table (Players) has 4 columns (Player_ID, Player_Name, Current_ELO and Player_Status). I want to show the games won/lost as well, but that info is in the Replays table, which has one row per each individual game. I can easily count the number of won games or lost games with the count(*) function:

select Winner, count(*) from Replays group by Winner;
select Loser, count(*) from Replays group by Loser;

However, I cannot find a way to join both tables so my final result is something like:

+-------------+-------------+-----------+------------+
| Player_Name | Current_ELO | Won games | Lost games |
+-------------+-------------+-----------+------------+
| John        | 1035        | 5         | 3          |
+-------------+-------------+-----------+------------+

My best guess is this, however returns too many results because I cannot group the "count()" by Winner/Loser individually for each count:

select Player_Name,  Current_ELO, Player_Status, count(Winner_Replays.Winner), count(Loser_Replays.Loser) from Players
inner join Replays as Winner_Replays on Winner_Replays.Winner = Players.Player_Name
inner join Replays as Loser_Replays on Loser_Replays.Loser = Players.Player_Name
group by Winner_Replays.Winner order by Current_ELO desc;

Ideally, as some players might have 0 wins or 0 losses, I would like that it returns 0 for those users, if that is possible at all. Thanks!

count join mysql
1 Answers

You can use the following using a INNER JOIN on a list of players with their result of the replay. You can use SUM with CASE to get the count of wins / defeats of each player.

SELECT Players.Player_Name, ANY_VALUE(Current_ELO), ANY_VALUE(Player_Status),
  SUM(CASE WHEN result = 'L' THEN 1 ELSE 0 END) AS Lost_Games, 
  SUM(CASE WHEN result = 'W' THEN 1 ELSE 0 END) AS Won_Games
FROM Players INNER JOIN (
  SELECT 'W' AS result, Winner AS Player_Name FROM Replays
  UNION ALL
  SELECT 'L', Loser FROM Replays
) win_lose ON Players.Player_Name = win_lose.Player_Name
GROUP BY Players.Player_Name
ORDER BY ANY_VALUE(Current_ELO) DESC;

You can also use the LEFT JOIN solution like this:

SELECT Players.Player_Name, ANY_VALUE(Current_ELO) AS Current_ELO, ANY_VALUE(Player_Status) AS Player_Status,
  COUNT(DISTINCT rLoser.ReplayID) AS Lost_Games, 
  COUNT(DISTINCT rWinner.ReplayID) AS Won_Games
FROM Players
  LEFT JOIN Replays AS rWinner ON rWinner.Winner = Players.Player_Name
  LEFT JOIN Replays AS rLoser ON rLoser.Loser = Players.Player_Name
GROUP BY Players.Player_Name
ORDER BY ANY_VALUE(Current_ELO) DESC;

demo on dbfiddle.uk

user_38408400
Related