Total number of matches played by player Udacity database SQL free course final project:-

I made 3 tables:


Players(pid, name, contact)  
Matches(mid, pid_a, pid_b, won)  
Matches_won(mid,pid)


I want to make a query so i can also be able to count the total matches played by player id.

I am able to make table with id, name, (number of matches won) with this query..

SELECT    players.pid, 
          players.name, 
          count(m_won.pid) as won 
FROM      players 
LEFT JOIN m_won 
ON        players.pid = m_won.pid 
GROUP BY  players.pid 
ORDER BY  players.pid;


I want to show playerid, playername, total wins and total matches played in 1 table. help me.

PostgreSQL code so you can also test yourself.

CREATE TABLE players(
  pid serial PRIMARY KEY, -- pid = PlayerID
  name text,
  contact bigint
);

CREATE TABLE matches(
  mid serial PRIMARY KEY, -- mid = MatchID
  pid_a int REFERENCES players (pid),
  pid_b int REFERENCES players (pid),
  winner int
);

-- In this table,
-- I am recording matchID and playerID
-- So i can keep track of players who won matches by matchID

-- m_won = matches_won
CREATE TABLE m_won(
  mid int REFERENCES matches (mid),
  pid int REFERENCES players (pid)
);

--RAW DATA TO INSERT INTO TABLES:-(dont change the values)
INSERT INTO players (name, contact) VALUES ('Dishank', 9803050043);
INSERT INTO players (name, contact) VALUES ('Farhan', 8943789678);
INSERT INTO players (name, contact) VALUES ('Rajat', 9742434546);
INSERT INTO players (name, contact) VALUES ('Ajay', 7865875467);

INSERT INTO matches (pid_a, pid_b, winner) VALUES (1, 2, 2);
INSERT INTO matches (pid_a, pid_b, winner) VALUES (1, 3, 3);
INSERT INTO matches (pid_a, pid_b, winner) VALUES (1, 4, 4);
INSERT INTO matches (pid_a, pid_b, winner) VALUES (2, 3, 2);
INSERT INTO matches (pid_a, pid_b, winner) VALUES (2, 4, 4);
INSERT INTO matches (pid_a, pid_b, winner) VALUES (3, 4, 3);

INSERT INTO m_won (mid, pid) VALUES (1, 2);
INSERT INTO m_won (mid, pid) VALUES (2, 3);
INSERT INTO m_won (mid, pid) VALUES (3, 4);
INSERT INTO m_won (mid, pid) VALUES (4, 2);
INSERT INTO m_won (mid, pid) VALUES (5, 4);
INSERT INTO m_won (mid, pid) VALUES (6, 3);


UPDATE_ UPDATE UPDATE UPDATE UPDATE UPDATE _UPDATE

I solved the answer 50%. (Thanks to @EVAN).

This query is counting total matches played by player.

SELECT p.player AS player, 
       p.name AS name, 
       count(*) AS total_played
FROM (
SELECT matches.pid_a AS player,
       players.name AS name
FROM   players 
JOIN   matches
ON     players.pid=matches.pid_a
UNION ALL
SELECT matches.pid_b AS player,
       players.name AS name
FROM   players 
JOIN   matches
ON     players.pid=matches.pid_b
) AS p
GROUP BY player, name
ORDER BY player;


And this query is showing total matches won by player

SELECT    players.pid AS id,
          players.name AS name,
          count(*) AS won
FROM      players
LEFT JOIN m_won
ON        players.pid = m_won.pid
GROUP BY  id
ORDER BY  id;


Now how should i combine them? so i get a table with coulums
"ID","Name","Total_Matches_Won","Total_Matches_played"

UPDATE_ UPDATE UPDATE UPDATE UPDATE UPDATE _UPDATE

Solved 100%(i am getting what i want to achieve), but still my code might be not reliable or can be further improved so keep the discussion active.

SELECT    C2.id, C2.name, C2.total_won, C1.total_played
FROM
(SELECT   p.id AS id,
          p.name AS name,
          count(*) AS total_played
FROM (
SELECT    matches.pid_a AS id,
          players.name AS name
FROM      players
JOIN      matches
ON        players.pid=matches.pid_a
UNION ALL
SELECT    matches.pid_b AS id,
          players.name AS name
FROM      players
JOIN      matches
ON        players.pid=matches.pid_b
) AS p
GROUP BY  id, name
ORDER BY  id
) AS C1,
(SELECT   players.pid AS id,
          players.name AS name,
          count(*) AS total_won
FROM      players
LEFT JOIN m_won
ON        players.pid = m_won.pid
GROUP BY  players.pid
) AS C2
GROUP BY  C2.id, C2.name, C2.total_won, C1.total_played
ORDER BY  C2.id;
First this is a horrible idea,

team_a int REFERENCES players (pid),


This will make everyone hate you. If there is no difference between a team and a player -- use the same name. Pick or have a difference.


  I want to make a query so i can also be able to count the total matches played by player id.


You need only query your matches table.

SELECT team, count(*)
FROM (
  SELECT team_a AS team
  FROM matches
  UNION ALL
    SELECT team_b AS team
    FROM matches
) AS t
GROUP BY team;


That is to say, these relationships are bidirectional but the schema isn't. If team foo plays team bar you can write that two separate ways. That is to say both of these are valid,

TeamA | TeamB | Wuteva
foo   | bar   | baz
bar   | foo   | quz


Update

In the future, try to put all of the things you want to do in the first question, but I've updated this to include the matches won

SELECT pid, count(*) AS played, count(is_won) FILTER (WHERE is_won)
FROM (
  SELECT pid_a AS pid, pid_a = winner AS is_won
  FROM matches
  UNION ALL
    SELECT pid_b AS pid, pid_b = winner AS is_won
    FROM matches
) AS t
GROUP BY pid;


As a separate note, good job on changing the names.