0

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.

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
  • I agree on BigInt, but I'm using WordPress and the user table is set by them. I thought to join with it my FKs had to also be BigInts. I don't understand the separation you're introducing. My user table has only tings about each user and my match results has only info about each individual match, when, who, results, scores, etc. – John Biddle Jun 1 '15 at 17:05
  • Creating a FK will require the datatypes to be the same, so if you want to keep the Wordpress default then continue to use Bigint, no significant harm there. Your user table is fine. I suggested the separation on the assumption that a match could have a group of users, and not just 2, in that case a column for each is fine. If not then the reference table may not be necessary. I may have misunderstood the question. Separating things out leaves room for more flexibility down the road, but depending on the complexity. It's not a requirement, just a suggestion. – Prime03 Jun 1 '15 at 17:17
  • Updated my answer with a more direct response to your question – Prime03 Jun 1 '15 at 17:27
  • Sorry to take so long to get back, life happens. This works, thank-you. – John Biddle Jun 2 '15 at 11:55
0

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.

  • Thanks, it works, but this seems to be essentially the same as Prime03's answer above. – John Biddle Jun 2 '15 at 11:59
  • yes it is same as above , i was just pointing to the exact solution to your problem – Tufan Chand Jun 2 '15 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.