CODEWITHSUNDEEP
sqlpostgresqljoincountsubquery
Luke Sheppard
Luke Sheppard

Reputation: 275

How to count two separate columns in the same table and sum them into a new column

I have two tables: playernames and matches.

SELECT * FROM playernames;
 id |       name       
----+------------------
 38 | Abe Lincoln
 39 | Richard Nixon
 40 | Ronald Reagan
(3 rows)

SELECT * FROM matches;
 match_id | winner | loser 
----------+--------+-------
        6 |     38 |    39
        8 |     38 |    39
        9 |     39 |    38
       10 |     38 |    39
       11 |     40 |    39
(5 rows)

I need to create a single query that returns four columns: id, name, wins, matches. But even with joins and subqueries, I can't seem to get all of it in one query. The closest I have come is to execute two separate queries. This is the query to calculate the total wins per player:

SELECT playernames.id, name, COUNT(*) AS wins 
FROM matches, playernames 
WHERE winner = playernames.id 
GROUP BY playernames.id, winner;
 id |       name       | wins 
----+------------------+------
 38 | Abe Lincoln      |    3
 39 | Richard Nixon    |    1
 40 | Ronald Reagan    |    1
(3 rows)

But I have to issue a separate query if I want to correctly calculate the total number of matches for each player:

SELECT playernames.id, name, COUNT(*) 
FROM matches, playernames 
WHERE playernames.id = winner 
OR playernames.id = loser 
GROUP BY playernames.id;
 id |       name       | count 
----+------------------+-------
 40 | Ronald Reagan    |     1
 38 | Abe Lincoln      |     4
 39 | Richard Nixon    |     5
(3 rows)

I'm avoiding cluttering up this question with the many incorrect attempts I've made at combining these into a single query. Can anyone advise me on how to properly combine these two queries into one?

Upvotes: 2

Views: 13055

Answers (1)

Mureinik
Mureinik

Reputation: 311143

I'd use one join to get all the matches a player participated in, and then count a case expression to extract only the ones he won:

SELECT    playernames.id, name, 
          COUNT(CASE playernames.id WHEN winner THEN 1 ELSE NULL END) AS wins, 
          COUNT(match_id) AS matches
FROM      playernames 
LEFT JOIN matches ON playernames.id IN (winner, loser)
GROUP BY  playernames.id, name;

Upvotes: 5

Related Questions