1

I am having two tables.

Table 1: Consists of name of the players and their id's.

tournament=> select * from Players;
 id |  name
----+--------
  1 | Rahul
  2 | Rohit
  3 | Ramesh
  4 | Roshan
  5 | Ryan
  6 | Romelu
  7 | Roman
  8 | Rampu
(8 rows)

Table 2:Consist of both the opponents playing each other, column1 contains the name of the winner and column2 loser. So this means that both taking part in that Match.

tournament=> select * from Matches;
 id | winner | loser
----+--------+-------
  1 |      1 |     2
  2 |      3 |     4
  3 |      5 |     6
  4 |      7 |     8
(4 rows)

Now I want to count number of matches played by different players, I have counted the number of matches won by players by following query.

SELECT Players.id, COUNT(Matches.winner) AS Points FROM Players LEFT JOIN (SELECT * from Matches) AS Matches ON Players.id = Matches.winner GROUP by Players.id Order by Points desc, Players.id;
 id | points
----+---
  1 | 1
  3 | 1
  5 | 1
  7 | 1
  2 | 0
  4 | 0
  6 | 0
  8 | 0
(8 rows)

But I am not able to get the logic of how should I calculate the number of matches played by each player?

From above Matches table we can see that each players has played once but I am not able to write that in psql.

Rahul Shrivastava
  • 1,391
  • 3
  • 14
  • 38

2 Answers2

1

A simple nested subquery is needed, as follows:

SELECT pl.id,
(
    SELECT COUNT(id)
    FROM Matches
    WHERE winner = pl.id OR loser = pl.id
) AS matches
FROM Players pl
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
John Doulamis
  • 21
  • 3
0

The following query counts the winner and loser ID values in two separate queries which are then UNIONed together. There is no worry of double counting because a team cannot play against itself, rather it can only play against another unique team. The outer query sums the team counts.

SELECT t.id, COUNT(t.id) AS numMatches FROM (
    SELECT winner AS id FROM Matches UNION ALL
    SELECT loser AS id FROM Matches
) t GROUP BY t.id
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360