[SOLVED] PostgreSQL - Left Join with Bad Count Output

PostgreSQL - Left Join with Bad Count Output

I'm trying to set up a simple set of tables for displaying the results of a tournament - I have the following structure:

CREATE TABLE players(
    id SERIAL PRIMARY KEY,
    name TEXT);

CREATE TABLE matches(
    id SERIAL PRIMARY KEY,
    player_one_id INTEGER REFERENCES players,
    player_two_id INTEGER REFERENCES players,
    winner_id INTEGER REFERENCES players);

And I've inputted some test data, as follows:

INSERT INTO players (name) VALUES ('Mike Jones');
INSERT INTO players (name) VALUES ('Albert Awesome');
INSERT INTO players (name) VALUES ('Sad Sally');
INSERT INTO players (name) VALUES ('Lonely Lenny');

INSERT INTO matches (player_one_id, player_two_id, winner_id) VALUES (1,2,1);
INSERT INTO matches (player_one_id, player_two_id, winner_id) VALUES (3,4,4);

I'm trying to perform a query which gives me the following results for each player:

id, name, matched_won, matches_played.

I have the following query thus far:

SELECT players.id, players.name, count(matches.winner_id) as matches_won
                               , count(matches.id) as matches_played
    FROM players left join matches
    ON players.id = matches.winner_id
GROUP BY players.id
ORDER BY matches_won DESC

And, unfortunately, I'm getting the incorrect output as follows (there should be 1 matches_played for each player):

 id |      name      | matches_won | matches_played 
----+----------------+-------------+----------------
  4 | Lonely Lenny   |           1 |              1
  1 | Mike Jones     |           1 |              1
  2 | Albert Awesome |           0 |              0
  3 | Sad Sally      |           0 |              0
(4 rows)

Now, I know the reason for this incorrect output is because of joining on players.id = matches.winner_id, but, my question is:

Is it possible to get these results with just one left join query? If so, how? I'd like to avoid doing multiple queries if possible.

Solution

Yes. First, you need to understand that count() simply counts the number of rows with non-NULL values, so your two counts should be the same.

To get the winner, use conditional aggregation:

SELECT p.id, p.name,
       sum(case when m.winner_id = p.id then 1 else 0 end) as matches_won,
       count(m.id) as matches_played
FROM players p left join
     matches m
     ON p.id in (m.player_one_id, m.player_two_id)
GROUP BY p.id
ORDER BY matches_won DESC;

You also need to fix the join condition. You cannot just join on the winner and expect to get the count of all the matches.