This is the definition of my playerStandings() function:
"""Returns a list of the players and their win records, sorted by wins.
The first entry in the list should be the player in first place, or a player
tied for first place if there is currently a tie.
A list of tuples, each of which contains (id, name, wins, matches):
id: the player's unique id (assigned by the database)
name: the player's full name (as registered)
wins: the number of matches the player has won
matches: the number of matches the player has played
db = connect()
c = db.cursor()
c.execute("select players.id,players.name, count((select matches.winner from matches where matches.winner = players.id))as win, count((matches.winner))as matches from players left join matches on players.id = matches.winner or players.id = matches.loser group by players.id order by win desc,id")
standing = c.fetchall()
standings = playerStandings()
[id1,id2,id3,id4,id5,id6] = [row for row in standings]
Don't put a
SUM() to count the rows that match an additional criteria.
SELECT p.id, p.name, SUM(CASE WHEN m.winner = p.id THEN 1 ELSE 0 END) AS win, COUNT(m.winner) AS matches FROM players as p LEFT JOIN matches AS m ON p.id IN (m.winner, m.loser) GROUP BY p.id ORDER BY win DESC, p.id