1

I'm new to SQL and trying to pick up the basics from an online tutorial. I'm stuck on a query and would appreciate some advice.

Currently, the goal is to create a player standing table for a tournament using queries. I've got two tables; players and matches.

tournament=> select * from players;
 id |       name       
----+------------------
 19 | Melpomene Murray
 20 | Randy Schwartz
 46 | Ricardo
(3 rows)


tournament=> select * from matches;
 winner_id | loser_id 
-----------+----------
        19 |       20
(1 row)

I need to aggregate the wins and matches to do this:

 id |       name       | wins | p_matches 
----+------------------+------+-----------
 19 | Melpomene Murray |    1 |         1
 20 | Randy Schwartz   |    0 |         1
 46 | Ricardo          |    0 |         0

Right now this is my best guess:

tournament=> select players.id, players.name, subq.wins, subq2.p_matches from players,
(select players.id,count(players.id) as wins from players,matches 
where matches.winner_id = players.id group by players.id) as subq,
(select count(*) as p_matches from matches) as subq2;

 id |       name       | wins | p_matches 
----+------------------+------+-----------
 19 | Melpomene Murray |    1 |         1
 20 | Randy Schwartz   |    1 |         1
 46 | Ricardo          |    1 |         1

A big chunk of the trouble stems from my subquery. When I run it individually, I'm getting a single row because of the conditional. I'd like to have it listing all ID's with the no-win rows holding zero.

tournament=> select players.id,count(players.id) as wins from players,matches 
where matches.winner_id = players.id group by players.id;
 id | wins 
----+------
 19 |    1

I've heard coalesce can do something like this, but I haven't been able to magic it into working. Thoughts?

Improve this question

3 Answers 3

Reset to default
1

There is also this, I'm not sure which performs better (likely better than multiple subqueries).

select p.id,
       p.name, 
       count(case when m.winner_id = p.id then 1 end) won, 
       count(m.*) total 
from players p 
left join matches m 
on m.winner_id = p.id 
or p.id = m.loser_id 
group by p.id, p.name;

This selects all the player records, and left joins the matches table (whether or not the player id exists in it), then groups the results, in which case one can use aggregate functions like count.

The first count aggregate function will only count rows with the player id in the winners column (won), the second one will count them if they are in either column.

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

1 Comment

This seems to do the trick, and I get the gist of it well enough. Thank you!
0

probably this?:

t=# with a as (
select p.*, w.w wins, coalesce(w.w, l.l) p_m
from players p
left outer join matches w on w.w = p.id
left outer join matches l on l.l = p.id
)
select id,name, count(wins), count(p_m)
from a
group by id, name
order by id;
 id |       name        | count | count
----+-------------------+-------+-------
 19 |  Melpomene Murray |     1 |     1
 20 |  Randy Schwartz   |     0 |     1
 46 |  Ricardo          |     0 |     0
(3 rows)
Improve this answer

1 Comment

Could I trouble you for a play-by-play explanation? I'm pretty new to the whole shebang, and I'd like to understand how your query works. For instance, I've never seen a query that starts with the command "with" instead of "select".
0

A simple way is using subqueries in your select clause:

select 
  id, 
  name,
  (select count(*) from matches m where p.id = m.winner_id) as wins,
  (select count(*) from matches m where p.id in (m.winner_id, m.loser_id)) as p_matches
from players p
order by id;
Improve this answer

2 Comments

I can follow the logic on this one, but I don't think I can access players from that section of the query. Rather, postgres doesn't think I can: ERROR: schema "p" does not exist
Yes, sorry, I missed the IN keyword, so PostgreSQL probably took p.id (m.winner_id, m.loser_id) to be a function, so p would be the schema.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.