Ian Hardingham <[email protected]> wrote:
> I have a badly designed structure for a table which records /games
> played/ by people. It looks like:
>
> id
> player1
> player2
> score
>
> If score > 0, player 1 won the game. If score < 0, player 2 won it.
> (Score of 0 is a draw).
>
> I wish to find the total record in games between two specific players.
> It seems to me that I would like to do the following:
>
> Select all games between the two players
> take a count of this
> Select from within the first select all games won by a certain player
I'm not quite sure what you are trying to achieve, but see if this helps, at
least as a starting point:
select
count(*) TotalGames,
sum(score > 0) GamesWonByPlayer1,
sum(score < 0) GamesWonByPlayer2,
sum(score = 0) Draws
from Games where player1 = ? and player2 = ?;
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
