Help with SQL statement to calculate a statistic

[CSMan]

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.

[magnav0x]

Is this in PHP? I would do something along the lines of a while loop. I'm not sure you will be able to accomplish what you want with one SQL statment, but if you can you will need to use the COUNT() function in SQL. If you get stuck let me know and I can help you out with some php/sql stuff to get you where you want to be.

[CSMan]

Hi magnav0x. No, I'm creating this in VB.NET. It's the only language I know so far.

I was primarily interested to see if it could be done strictly in SQL. Looks like that won't happen. I think I figured out how to do it with the SQL I posted before, using the results from the SQL statement in my code and then writing the new data to the database.

Thanks for the offer of help. I appreciate it.