2

I have two tables: playernames and matches.

SELECT * FROM playernames;
 id |       name       
----+------------------
 38 | Abe Lincoln
 39 | Richard Nixon
 40 | Ronald Reagan
(3 rows)

SELECT * FROM matches;
 match_id | winner | loser 
----------+--------+-------
        6 |     38 |    39
        8 |     38 |    39
        9 |     39 |    38
       10 |     38 |    39
       11 |     40 |    39
(5 rows)

I need to create a single query that returns four columns: id, name, wins, matches. But even with joins and subqueries, I can't seem to get all of it in one query. The closest I have come is to execute two separate queries. This is the query to calculate the total wins per player:

SELECT playernames.id, name, COUNT(*) AS wins 
FROM matches, playernames 
WHERE winner = playernames.id 
GROUP BY playernames.id, winner;
 id |       name       | wins 
----+------------------+------
 38 | Abe Lincoln      |    3
 39 | Richard Nixon    |    1
 40 | Ronald Reagan    |    1
(3 rows)

But I have to issue a separate query if I want to correctly calculate the total number of matches for each player:

SELECT playernames.id, name, COUNT(*) 
FROM matches, playernames 
WHERE playernames.id = winner 
OR playernames.id = loser 
GROUP BY playernames.id;
 id |       name       | count 
----+------------------+-------
 40 | Ronald Reagan    |     1
 38 | Abe Lincoln      |     4
 39 | Richard Nixon    |     5
(3 rows)

I'm avoiding cluttering up this question with the many incorrect attempts I've made at combining these into a single query. Can anyone advise me on how to properly combine these two queries into one?

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
Luke Sheppard
  • 275
  • 1
  • 2
  • 13

1 Answers1

5

I'd use one join to get all the matches a player participated in, and then count a case expression to extract only the ones he won:

SELECT    playernames.id, name, 
          COUNT(CASE playernames.id WHEN winner THEN 1 ELSE NULL END) AS wins, 
          COUNT(match_id) AS matches
FROM      playernames 
LEFT JOIN matches ON playernames.id IN (winner, loser)
GROUP BY  playernames.id, name;
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • Sure enough, that did it. Thanks. Interestingly, your solution contains elements that were not covered in the class I'm taking. But I'm not able to confer with the instructor or TA as their forum is offline until further notice. For now I have doubts that this could have been solved with the simple JOINs and subqueries taught in the class. And I was not able to figure it out from the postgresql documentation. – Luke Sheppard Apr 29 '15 at 07:16
  • In fact I just added a player with 0 matches and you are right, he does not appear in the output. I don't know if this is a problem with the query as proposed by Mureinik or with my schema. A player with 0 matches does not have any rows in the matches table, only a row in the playernames table. – Luke Sheppard Apr 29 '15 at 07:37
  • @LukeSheppard indeed, the query did not handle players with no matches. Using an `left join`, like Pavel suggested, handles them. – Mureinik Apr 29 '15 at 07:48
  • Yes. The `LEFT JOIN` did it. Thanks again. – Luke Sheppard Apr 29 '15 at 08:04