getting PFR for use in external script

[locutus]

Hi,

I'm trying to extract PFR % for use in external AHK script. In PT3 'cnt_pfr' is defined as

Code: Select all

sum(if[tourney_holdem_hand_player_statistics.cnt_p_raise > 0, 1, 0])

However this doesn't work directly as a SQL statement - looks like there is no built-in "if" function.

I've got a test statement that gives me VPIP and a slightly-inflated PFR (because it is tallying multiple raises preflop instead of counting the whole action as one instance of PFR):

Code: Select all

SELECT player_name, COUNT(g.id_player) AS hands,
   sum(cast(flg_vpip as int4))*100/count(g.id_player) AS vpip,
   sum(cast(cnt_p_raise as int4)*100/count(g.id_player) AS pfr
FROM tourney_holdem_hand_player_statistics g, player p
WHERE p.id_player = g.id_player GROUP BY player_name
ORDER BY hands DESC;


Thanks for any help.

EDIT: Found the answer from an example query in another post. Looks like PostgreSQL has a funky 'case when' syntax, this gives me what I need:

Code: Select all

...
   sum((case when(cnt_p_raise > 0) then 1 else 0 end))*100/count(g.id_player) AS pfr
...


EDIT 2: And thanks _dave_ for the quick reply. :D

[_dave_]

you need to do "CASE (cnt_p_raise > 0 ) THEN 1 ELSE 0" to flatten the multiple raises to 1.

BTW, if you are looking for how to get stats from PT3 into an AHK, read my AHK-HUD source code in the general forum - lots of examples in there :)