PostgreSQL - Left Join with Bad Count Output

Question

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.

Gordon Linoff · Accepted Answer · 2015-02-12 12:57:11Z

3

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.

answered Feb 12, 2015 at 12:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jarlh · Accepted Answer · 2015-02-12 13:08:36Z

1

Sub-select solution:

SELECT players.id, players.name,
       (select count(*)
        from matches
        where matches.winner_id = players.id) as matches_won,
       (select count(*)
        from matches
        where players.id in (player_one_id, player_two_id)) as matches_played
FROM players
ORDER BY matches_won DESC

answered Feb 12, 2015 at 13:08

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Comments

Frank Heikens · Accepted Answer · 2015-02-12 13:21:35Z

1

In addition to Gordon's answer, you can use COUNT() instead of SUM(), using NULLIF or FILTER (filter as of PostgreSQL 9.4):

Using NULLIF() because NULL doesn't count when using the column name:

SELECT p.id, p.name,
       count(nullif(m.winner_id <> p.id, false)) 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;

And using FILTER:

SELECT p.id, p.name,
       count(*) filter (WHERE m.winner_id = p.id) 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;

edited Feb 12, 2015 at 13:21

answered Feb 12, 2015 at 13:16

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Comments

akhil vangala · Accepted Answer · 2015-02-12 13:35:20Z

0

SELECT p.name,COUNT(m.player_one_id)+ COUNT(m1.player_two_id) AS num_of_matches_played
,COUNT(m2.winner_id) AS num_of_matches_won FROM players p 
LEFT OUTER JOIN matches m ON p.id = m.player_one_id
LEFT OUTER JOIN matches m1 ON p.id = m1.player_two_id
LEFT OUTER JOIN matches m2 ON p.id = m2.winner_id
GROUP BY p.name

answered Feb 12, 2015 at 13:35

akhil vangala

1,0631 gold badge10 silver badges11 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - Left Join with Bad Count Output

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related