I have two tables players and scores. On the player table I have two fields, name and id (PK) On the scores I have several fields [id_score(PK),winner and looser are foreign key to the id of the player] id tournament and id match.
Players
id_players | name
------------+-----------
41 | Antonia
42 | Roberto
43 | Luis
44 | Pedro
45 | Fernando
46 | Alejandra
47 | Rene
48 | Julieta
Scores
id_score | id_matches | winner | looser | id_tournament | round
----------+------------+--------+--------+---------------+-------
19 | 22 | 41 | 42 | 21 | 1
20 | 23 | 43 | 44 | 21 | 1
21 | 24 | 45 | 46 | 21 | 1
22 | 25 | 47 | 48 | 21 | 1
23 | 26 | 43 | 41 | 21 | 2
24 | 27 | 45 | 47 | 21 | 2
25 | 28 | 42 | 44 | 21 | 2
26 | 29 | 48 | 46 | 21 | 2
27 | 30 | 43 | 45 | 21 | 3
28 | 31 | 42 | 48 | 21 | 3
29 | 32 | 41 | 47 | 21 | 3
30 | 33 | 46 | 44 | 21 | 3
I'm trying to get a query that contains the following:
id of the player
name of the player
number of wins
total games
Currently I have these query. It kind of works. When I do the order by, it's not returning items on the correct orders
select p.id_players,p.name,count (s.winner) as wins, max(s.round)
from players p
left join scores s on s.winner = p.id_players
group by id_players, s.winner
order by s.winner desc;
id_players | name | wins | max
------------+-----------+------+-----
41 | Antonia | 2 | 3
42 | Roberto | 2 | 3
43 | Luis | 3 | 3
45 | Fernando | 2 | 2
46 | Alejandra | 1 | 3
47 | Rene | 1 | 1
48 | Julieta | 1 | 2
44 | Pedro | 0 |
Sometimes, it is easier to use subqueries:
select p.*,
(select count(*)
from scores s
where p.id_players in (s.winner, s.loser)
) as GamesPlayed,
(select count(*)
from scores s
where p.id_players in (s.winner)
) as GamesWon
from players p
order by GamesWon desc;
If the maximum of the round is the number of games played, then similar logic will get that.