Finding Standings Table in a tournament

Question

I have 2 tables

create table players
(name text,
 id serial primary key);

create table matches
 (winner integer references players(id),
  loser integer references players(id),
  id serial primary key);

I have to make a table called "standings" containing:

player_id,player_name,total_wins,total_matches

How to proceed?

Answer 1

Something like this:

select p.id, p.name, 
       count(w.id) as total_wins, 
       count(l.id) + count(w.id) as total_matches
from players p 
  left join matches w on w.winner = p.id
  left join matches l on l.loser = p.id
group by p.id, p.name;

Online example: http://rextester.com/EHDCG19917