PDA

View Full Version : SQL: How do I get VPIP/PFR stats when 7+ are playing?



anter
07-30-2010, 04:44 PM
I've figured out how to calculate VPIP/PFR stats for in SQL (see below). I'd like to be able to calculate VPIP/PFR when 7-10 players are playing, when 4-6 players are playing and when 2-3 players are playing. Is this possible to do?



SELECT playername, sum(totalhands), sum(vpiphands), sum(pfrhands)
FROM compiledplayerresults_month
JOIN compiledresults_month
ON compiledplayerresults_month.compiledplayerresults_ id = compiledresults_month.compiledplayerresults_id
JOIN players
ON compiledresults_month.player_id = players.player_id
GROUP BY playername

redlotus
08-11-2010, 02:32 PM
I'm assuming that you want to run this against the db directly through pgAdmin:


SELECT playername,
(case numberofplayers when 2 then 'Heads-up' when 3 then 'Heads-up' when 4 then 'Shorthand' when 5 then 'Shorthand' when 6 then 'Shorthand' else 'Fullring' end) AS NUMPLAYERS,
COUNT(pokerhand_id),
AVG(case didvpip when true then 100 else 0 end),
AVG(case didpfr when true then 100 else 0 end)
FROM playerhandscashkeycolumns
JOIN players USING (player_id)
WHERE preflopaction_id<>-1
GROUP BY playername, NUMPLAYERS
ORDER BY playername, NUMPLAYERS

Change playerhandscashkeycolumns to playerhandstourneykeycolumns if you want to query tournament hands. Obviously, you can also change the descriptions to anything you'd like in the second column.

Sorry if I'm late to the ballgame on this for you--I don't browse this forum very often.

Hope this helps,
-red