Big Query

[TheProdigy]

Hey,

I am trying to figure out how to include these stats in a query to make them all show up for me at once:

[quotesvj]Players Hands VPIP PFR 3Bet Foldto 3bet Raise 3bet WTSD% Cbet % Cbet/Fold % Cbet/Call Cbet/Raise Cbet after 3b Pre FoldtoflopCbet

Shown for players that have more than 3100 hands.[/quotesvj]

I don't really expect anyone to write out the whole query, but if you have time and can help with any parts that would be great help!

Thanks!

[_dave_]

Done most of it for you, please clarify : "Cbet/Fold % Cbet/Call Cbet/Raise"

just reaction to being cbetted? it cant be cos of "FoldtoflopCbet"

Please write the list of desired stats with a separator (like newline) :)

[TheProdigy]

I didn't need any separator or anything, just all in a list and I'll put it in a big sheet on excel.

What I want is after the person cbets. So if he cbets and gets check-raised, how often does he fold? call? raise?

So if I bet preflop, a guy calls in the blinds, and then he checks to me on flop and I cbet, if he raises and I call it would be cbet/call%, if I fold, it would be cbet/fold%

Thanks again!

[_dave_]

Thanks fo clarification.

I really meant for you to fix your list with a separator :) but nevermind, since you explained it now :)

So these should be:

Raised Cbet/Fold %
Raised Cbet/Call %
Rasied Cbet/Raise %

That's actually quite tricky...
EDIT: No it isn't - I think I got it :) - need to test now...

[TheProdigy]

You are the man. If I wasn't broke I would be sending donations.

[_dave_]

Try this:

[codeulg]
--Players - Hands - Money - VPIP - PFR - 3Bet - Foldto 3bet - Call 3bet - Raise 3bet - Foldto 3bet when PFR - Call 3bet when PFR - Raise 3bet when PFR- WTSD% - Cbet % - Cbet/Fold when CR - Cbet/Call when CR - Cbet/Raise when CR - Cbet after 3b Pre - FoldtoflopCbet

SELECT
p.player_name,
COUNT(hhps.id_player) AS hands,
SUM (amt_won) AS monies,
AVG (CASE WHEN flg_vpip THEN 1 ELSE 0 END)*100 AS vpip,
AVG (CASE WHEN cnt_p_raise >= 1 THEN 1 ELSE 0 END)*100 AS pfr,
AVG (CASE WHEN flg_p_3bet THEN 1 WHEN flg_p_3bet_opp THEN 0 END)*100 AS threebet,
AVG (CASE WHEN enum_p_3bet_action = 'F' THEN 1 WHEN flg_p_3bet_def_opp THEN 0 END)*100 AS fold_to_threebet,
AVG (CASE WHEN enum_p_3bet_action = 'C' THEN 1 WHEN flg_p_3bet_def_opp THEN 0 END)*100 AS call_to_threebet,
AVG (CASE WHEN enum_p_3bet_action = 'R' THEN 1 WHEN flg_p_3bet_def_opp THEN 0 END)*100 AS raise_to_threebet,
AVG (CASE WHEN enum_p_3bet_action = 'F' AND flg_p_first_raise THEN 1 WHEN flg_p_3bet_def_opp AND flg_p_first_raise THEN 0 END)*100 AS fold_facing_threebet_as_pfr,
AVG (CASE WHEN enum_p_3bet_action = 'C' AND flg_p_first_raise THEN 1 WHEN flg_p_3bet_def_opp AND flg_p_first_raise THEN 0 END)*100 AS call_facing_threebet_as_pfr,
AVG (CASE WHEN enum_p_3bet_action = 'R' AND flg_p_first_raise THEN 1 WHEN flg_p_3bet_def_opp AND flg_p_first_raise THEN 0 END)*100 AS raise_facing_threebet_as_pfr,
AVG (CASE WHEN flg_showdown AND flg_vpip THEN 1 WHEN flg_vpip THEN 0 END)*100 AS wtsd,
AVG (CASE WHEN flg_f_cbet THEN 1 WHEN flg_f_cbet_opp THEN 0 END)*100 AS cbet,
AVG (CASE WHEN flg_f_cbet AND flg_f_3bet_opp AND flg_f_fold AND flg_f_4bet_def_opp = 'f' THEN 1 WHEN flg_f_cbet AND flg_f_3bet_opp AND flg_f_4bet_def_opp = 'f' THEN 0 END)*100 AS fold_when_cbet_raise,
AVG (CASE WHEN flg_f_cbet AND flg_f_3bet_opp AND cnt_f_call >=1 AND flg_f_4bet_def_opp = 'f' THEN 1 WHEN flg_f_cbet AND flg_f_3bet_opp AND flg_f_4bet_def_opp = 'f' THEN 0 END)*100 AS call_when_cbet_raise,
AVG (CASE WHEN flg_f_cbet AND flg_f_3bet_opp AND cnt_f_raise >=1 AND flg_f_4bet_def_opp = 'f' THEN 1 WHEN flg_f_cbet AND flg_f_3bet_opp AND flg_f_4bet_def_opp = 'f' THEN 0 END)*100 AS raise_when_cbet_raise,
AVG (CASE WHEN flg_f_cbet AND flg_p_3bet THEN 1 WHEN flg_f_cbet_opp AND flg_p_3bet THEN 0 END)*100 AS cbet_after_pf_3bet


FROM player p, holdem_hand_player_statistics hhps
WHERE p.id_player = hhps.id_player
GROUP BY p.player_name
HAVING COUNT(hhps.id_player) > 100
ORDER BY COUNT(hhps.id_player) DESC;
[/codeulg]

Note I have added a column for monies (surely you want to know if the player is winning or losing :) Also I have done both forms of 3bet stats - the std. ones first, the (ones I argued for) second, where one is 3bet as the open-raiser (thus they sum to 100% of actions). Same goes for the raised CBET %, I ignored what happens after (4 / 5 bets) - so these are a 100% Sum also.

I added Call 3bet too.

I'm not sure how WTSD should work... I did WTSD when VPIP - maybe WTSD WSF & VPIP would be more useful?

I has only a small database so I set minimum hands to 100 - you will want to change this.

You will need the latest PT3 version for the cbet stats to be correct.

dave.

[TheProdigy]

Good to hear about them going to 100%. Very good for ev calcs.

Also, I don't quite understand which 3bets you did? I'm mainly looking for preflop 3bets, not postflop.

Also, how can I do it to only select players with vpip >11.9 and <22 for example.

[_dave_]

[quote="TheProdigy"l0j]
I don't quite understand which 3bets you did? I'm mainly looking for preflop 3bets, not postflop.[/quotel0j]
It's all pre-flop (aside from the cbets obviously.

It is either PT3 style 3bets - where open folding when facing a re-raised pot, or cold-calling / open 4betting etc. are counted - or a style restricted to where player has made the first raise already (so it is *their* open being 3bet) - the more useful of the two I think.

[quotel0j]
Also, how can I do it to only select players with vpip >11.9 and <22 for example.[/quotel0j]
[/quote]

look near the end where is says:
[codel0j]
HAVING COUNT(hhps.id_player) > 100
[/codel0j]
You can add other stuff in there - just copy paste from the above SQL - for your example, it would be:
[codel0j]
HAVING COUNT(hhps.id_player) > 100 AND AVG (CASE WHEN flg_vpip THEN 1 ELSE 0 END)*100 > 11.9 AND AVG (CASE WHEN flg_vpip THEN 1 ELSE 0 END)*100 <22
[/codel0j]

[TheProdigy]

This is great man thanks.

I must say I *heart* you in a totally nohomo way.

[TheProdigy]

what is the best way to export to excel?