I'm trying to solve this query where I have two players with game scores. The query should return a chart of the players with the numbers of games and if the score was 3:0 or 0:3 then the player that score 3 gets 3 point otherwise any winning is for 2 points
My table:
player1 player2 player1score player2score
------- ------- ------------- ------------
john lee 3 0
maria andy 1 3
andy john 1 3
the desired table should be like this:
players gamesplayed points
------- ----------- ------
john 2 5
andy 2 2
maria 1 0
lee 1 0
I have this query:
SELECT DISTINCT players, count(*) gamesplayed
FROM (
SELECT player1 AS players
FROM table
UNION
SELECT player2 AS players
FROM table
) players
But the query above returns the count of all players together and not individually in gamesplayed.
Can anyone see the problem? I'm also not sure hot implement the points column.
Many thanks