Counting on Two Different Columns in the Same Table

Question

I run a pool league and have kept stats in Excel. I want to automate via a website and MySQL will be my primary data analysis tool, though my knowledge there is only at the beginning of a long journey.

I have 2 tables (only showing relevant tables and relevant fields)

users:
--------------------
id            BigInt
display_name  VarChar(250)


match_results:
---------------------
id            BigInt
winner_id     BigInt
loser_id      BigInt

I want to produce a result set to show as a table of standings, like this:

NAME          WINS     LOSSES
-------------------------------
User2dn        12         7
User1dn        11         8
User5dn        10         8
etc.

I can manage the ORDER BY

I have other queries where I have successfully joined these two tables to get the display names for both winner_id and loser_id by using 2 different aliases for the same user table and that worked great, but I'm totally baffled here.

What I really need is more complicated than this, since I need to report from multiple other columns in match_results, the max of high_run (as a winner or loser) and bonus points which will be a sum irrespective of winner or loser status. I wanted to keep this question as simple as I could so I could understand it, and then build up the additional complications later. Hope that's OK.

Answer 1

John,

If you have a table of users, and a table of matches, you should probably have a reference table between the two of them, that has a many-to-many relationship.

users:
--------------------
id (PK)       Int
display_name  VarChar(250)

matches:
---------------------
matchId (PK)   Int
...
...
  -- Other relevant attributes about the match

results:
---------------------
ResultId (PK)      Int
Name           Varchar(100) 
  -- Win, loss, whatever else

match_results:
---------------------
matchId (PK, FK)  Int
UserId (PK, FK)   Int
ResultId (PK, FK) Int

For every match, you should have a record for every participant in that match, and their relevant result. Unless you plan on having more than 2 billion matches or users, I would stick to using Int.

Now you can join across these tables and SUM() or MAX() or whatever aggregate you would prefer.

SELECT u.Name , 
   COUNT( CASE
          WHEN r.Name = 'Win' THEN 1
          END )AS Wins , 
   COUNT( CASE
          WHEN r.Name = 'Loss' THEN 1
          END )AS Losses
FROM match_results AS mr INNER JOIN users AS u ON mr.userId = u.id
                       INNER JOIN matches AS m ON mr.matchId = m.id
                       INNER JOIN results AS r ON mr.resultId = r.id;

Edit:

Using your existing schema, you can use sub queries to aggregate your statistics

  SELECT u.display_name AS NAME, 
    (SELECT COUNT(1) FROM match_results mr WHERE mr.winner_id = u.id) AS Wins,
    (SELECT COUNT(1) FROM match_results mr WHERE mr.loser_id = u.id) AS Losses
  FROM users u

Answer 2

Try this one

select u.id, u.display_name, 
(select count(winner_id) from match_results where winner_id=u.id) as w,
(select count(loser_id) from match_results where loser_id=u.id) as l
from users u

By the above query your current problem will be solved.

If you need to retrieve more column than you might be need to make some changes.