CSMan
04-03-2004, 03:29 PM
Sorry if this question is in the wrong place, as it doesn't deal with stats in the distributed computing sense. I figured it would be a good place to post my question as it involves figuring out how to calculate a stat, which you guys are good at! Feel free to move or delete it if needed.
I am making a card game my family likes to play and am now trying to develop the stats system. I want to show various stats but the one I'm having problems with is showing a players win/loss ratio against other players.
I have a table called GameScores that has these columns: GameNumber, PlayerID, Score, and WonLost. I need to figure out which games two particular players have both played in. Then out of those games find how many times each player has won. From there its easy to figure out the win/loss ratio.
I was hoping to get the SQL to do this down to one statement, but I don't know if that's possible. Here's what I came up with so far for PlayerIDs 1 and 2:
SELECT PlayerID
FROM GameScores
WHERE WonLost = TRUE
AND GameNumber IN
(SELECT DISTINCT GameNumber FROM GameScores WHERE GameNumber IN
(SELECT GameNumber FROM GameScores WHERE PlayerID = 1) AND GameNumber IN
(SELECT GameNumber FROM GameScores WHERE PlayerID = 2))
That gives me a list of the winners of the games where both players 1 and 2 have played in. Then I would have to count how many times each player has won, not counting the games where a 3rd player has won. I would love to be able to figure it out and store it in another table all in one statement but I don't know if that's possible. I've only been working with SQL for a few months.
If anyone has any ideas, I would sure appreciate it.
I am making a card game my family likes to play and am now trying to develop the stats system. I want to show various stats but the one I'm having problems with is showing a players win/loss ratio against other players.
I have a table called GameScores that has these columns: GameNumber, PlayerID, Score, and WonLost. I need to figure out which games two particular players have both played in. Then out of those games find how many times each player has won. From there its easy to figure out the win/loss ratio.
I was hoping to get the SQL to do this down to one statement, but I don't know if that's possible. Here's what I came up with so far for PlayerIDs 1 and 2:
SELECT PlayerID
FROM GameScores
WHERE WonLost = TRUE
AND GameNumber IN
(SELECT DISTINCT GameNumber FROM GameScores WHERE GameNumber IN
(SELECT GameNumber FROM GameScores WHERE PlayerID = 1) AND GameNumber IN
(SELECT GameNumber FROM GameScores WHERE PlayerID = 2))
That gives me a list of the winners of the games where both players 1 and 2 have played in. Then I would have to count how many times each player has won, not counting the games where a 3rd player has won. I would love to be able to figure it out and store it in another table all in one statement but I don't know if that's possible. I've only been working with SQL for a few months.
If anyone has any ideas, I would sure appreciate it.