CODEWITHSUNDEEP
sqlpostgresqlaggregate-filter
jtyler
jtyler

Reputation: 1105

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.

Upvotes: 1

Views: 144

Answers (4)

akhil vangala
akhil vangala

Reputation: 1053

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

Upvotes: 0

Frank Heikens
Frank Heikens

Reputation: 127086

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;

Upvotes: 1

jarlh
jarlh

Reputation: 44766

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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.

Upvotes: 3

Related Questions