driftdrift driftdrift - 1 month ago 10
SQL Question

How do i join these two PSQL queries together correctly?

I am trying to return a query that will show how many times a player has played in a game and how many time he/she has won. This is what I have so far, stuck on the last join query

Link to my SQL fiddle:
http://sqlfiddle.com/#!15/46b2a/52

I have two tables:

CREATE TABLE player(
id serial PRIMARY KEY NOT NULL,
name varchar(255) NOT NULL
);

CREATE TABLE match(
id serial PRIMARY KEY,
winner serial REFERENCES player(id) NOT NULL,
loser serial REFERENCES player(id) NOT NULL CHECK (loser != winner)
);

CREATE SEQUENCE playerid_sequence
start 1
increment 1;

CREATE SEQUENCE matchid_sequence
start 1
increment 1;


I populated my tables with some example insertions:

--Player Insertion
INSERT INTO player VALUES(nextval('playerid_sequence'), 'Kevin');
INSERT INTO player VALUES(nextval('playerid_sequence'), 'Dennis');
INSERT INTO player VALUES(nextval('playerid_sequence'), 'George');
INSERT INTO player VALUES(nextval('playerid_sequence'), 'Michael');

--Match Insertion
INSERT INTO match VALUES(nextval('matchid_sequence'), 1, 2);
INSERT INTO match VALUES(nextval('matchid_sequence'), 1, 3);
INSERT INTO match VALUES(nextval('matchid_sequence'), 1, 4);
INSERT INTO match VALUES(nextval('matchid_sequence'), 2, 3);


I created two SQL queries:

--How many did a player win?
SELECT player.id, player.name, count(player.name) as wins FROM player, match
WHERE player.id = match.winner GROUP by player.name, player.id ORDER BY wins DESC;

--How many matches did a particular player participate in?
SELECT player.id, player.name, count(player.name) as matches_played
FROM player, match
WHERE player.id = match.winner OR player.id = match.loser
GROUP by player.name, player.id
ORDER by matches_played DESC;


I made an attempt to join them:

SELECT
*
FROM
(SELECT player.id, player.name, count(player.name) as wins FROM player, match
WHERE player.id = match.winner GROUP by player.name, player.id ORDER BY wins DESC) t1
FULL OUTER JOIN
(SELECT player.id as id, count(player.name) as matches_played
FROM player, match
WHERE player.id = match.winner OR player.id = match.loser
GROUP by player.id
ORDER by matches_played DESC) t2
ON t1.id = t2.id;


The above join query only returns the set of players from t1, while I want it to return all of the players from t2.

I want:
player id| name| wins| total matches played for all the players, with 0's in the win column if they haven't won.

I have:

id name wins id matches_played
1 Kevin 3 1 3
2 Dennis 1 2 2
(null) (null) (null) 4 1
(null) (null) (null) 3 2

Answer

I think the query can be simplified to:

select p.id, p.name,
       count(case when m.winner = p.id then 'X' end) as wins,
       count(m.id) as matches_played
  from player p
  left join match m
    on m.winner = p.id or m.loser = p.id
 group by p.id, p.name;

SQL Fiddle Demo

The key is that you want to left join on the match table. The rest is simple conditional aggregation.