SQL Help

[payjo]

I have a problem. Here's my table:

table plays:
playersname
gameid
earnings
finish

The finish row is an enum of 0,1,2

well, i would like a number of times a player finishes 1st (or 1) and second (or 2).

i know the easy way, but my sql query looks like this right now:

SELECT COUNT(gameid) AS total_games, SUM(earnings) as total_earnings,
(SUM(earnings)-(COUNT(gameid)*10)) as total_balance, playersname,
FROM PLAYS
GROUP BY playersname
HAVING COUNT(gameid) > 1

I don't know how to incorporate the finish query. Should I be using a subquery? Please help!

[Lars_Berg]

SELECT COUNT(gameid) AS total_games, SUM(earnings) as total_earnings,
(SUM(earnings)-(COUNT(gameid)*10)) as total_balance, playersname,,
sum(case when finish = 1 then 1 else 0 end) as firstFinish,
sum(case when finish = 2 then 1 else 0 end) as secondFinish
FROM PLAYS
GROUP BY playersname
HAVING COUNT(gameid) > 1 

[payjo]

Thanks! That did the trick. Where can I learn more of this?