Making custom pot column to account for rake

[ttesone]

When playing at Bodog/Ignition, the site substracts rake from the pot on each street played. So, for example, if a players makes a flop 75% cbet, it won't show on PT4 as 75% because rake won't be taken into account. I want to correct that using the site's rake structure. I tried making this column, but I get the error "Error: Unable to execute query: Fatal Error; Reason: ERROR: column "cash_hand_summary.cnt_players" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ... 0 end))) as "cnt_f_cbet_72_80_rake", ((case when(cash_hand_..."

What I want "pot_menos_rake" to do, is to recalculate the pot to substract the rake. Since rake structure changes depending on the numbers of players dealt into the hand, I need to know how many players there are. After that I substract the rake, taking into account rake cap. If I activate "group by", it doesn't work either although I don't get any error. What should I do?

pot_menos_rake:

if[cash_hand_summary.cnt_players = 2, if[cash_hand_summary.amt_pot_f < 20, 0.95 * cash_hand_summary.amt_pot_f, cash_hand_summary.amt_pot_f - (1 * cash_limit.amt_bb)],
if[cash_hand_summary.cnt_players = 3, if[cash_hand_summary.amt_pot_f < 40, 0.95 * cash_hand_summary.amt_pot_f, cash_hand_summary.amt_pot_f - (2 * cash_limit.amt_bb)],
if[cash_hand_summary.cnt_players >= 4 AND cash_hand_summary.cnt_players <= 5, if[cash_hand_summary.amt_pot_f < 60, 0.95 * cash_hand_summary.amt_pot_f, cash_hand_summary.amt_pot_f - (3 * cash_limit.amt_bb)],
if[cash_hand_summary.cnt_players >= 6, if[cash_hand_summary.amt_pot_f < 80, 0.95 * cash_hand_summary.amt_pot_f, cash_hand_summary.amt_pot_f - (4 * cash_limit.amt_bb)],0]]]]

[ttesone]

Forgot to add, I then use that "pot_menos_rake" column in another column called "cnt_f_cbet_72_80_rake" this way:

sum(if[cash_hand_player_statistics.flg_f_cbet AND cash_hand_player_statistics.amt_bet_f / pot_menos_rake >= 0.72 AND cash_hand_player_statistics.amt_bet_f / pot_menos_rake < 0.8, 1, 0])

Then I made a stat called "Cbet Flop 72-80 Percentage":
(cnt_f_cbet_72_80_rake / cnt_f_cbet) * 100

[Flag_Hippo]

sum(if[cash_hand_player_statistics.flg_f_cbet AND cash_hand_player_statistics.amt_bet_f / pot_menos_rake >= 0.72 AND cash_hand_player_statistics.amt_bet_f / pot_menos_rake < 0.8, 1, 0])

Unfortunately column expressions can't reference other columns so that's not going to work. Also cash_hand_player_statistics.amt_bet_f is the total amount wagered by the player on the flop so it will include any subsequent calls or raises. The size of a players flop bet is cash_hand_player_statistics.amt_f_bet_made so you could build a column like this and do the math there:

Code: Select all

sum(if[cash_hand_player_statistics.flg_f_cbet and cash_hand_summary.cnt_players = 2 and cash_hand_summary.amt_pot_f < 20 and (cash_hand_player_statistics.amt_f_bet_made / cash_hand_summary.amt_pot_f * .95) between 0.72 and 0.8, 1, 0])

You can include additional conditions or create separate columns for other rake conditions and then add the columns together in a custom statistic.

[ttesone]

Thanks for the help!

I made this column following your advice (cnt_r_cbet_60_72_rake):

sum(if[cash_hand_player_statistics.flg_r_cbet and cash_hand_summary.cnt_players = 2 and ((cash_hand_summary.amt_pot_r < 20 and (cash_hand_player_statistics.amt_r_bet_made / (cash_hand_summary.amt_pot_r * .95)) between 0.60 and 0.7199) or (cash_hand_summary.amt_pot_r >= 20 and (cash_hand_player_statistics.amt_r_bet_made / (cash_hand_summary.amt_pot_r - 1)) between 0.60 and 0.7199)), 1,
if[cash_hand_player_statistics.flg_r_cbet and cash_hand_summary.cnt_players = 3 and ((cash_hand_summary.amt_pot_r < 40 and (cash_hand_player_statistics.amt_r_bet_made / (cash_hand_summary.amt_pot_r * .95)) between 0.60 and 0.7199) or (cash_hand_summary.amt_pot_r >= 40 and (cash_hand_player_statistics.amt_r_bet_made / (cash_hand_summary.amt_pot_r - 2)) between 0.60 and 0.7199)), 1,
if[cash_hand_player_statistics.flg_r_cbet and cash_hand_summary.cnt_players between 4 and 5 and ((cash_hand_summary.amt_pot_r < 60 and (cash_hand_player_statistics.amt_r_bet_made / (cash_hand_summary.amt_pot_r * .95)) between 0.60 and 0.7199) or (cash_hand_summary.amt_pot_r >= 60 and (cash_hand_player_statistics.amt_r_bet_made / (cash_hand_summary.amt_pot_r - 3)) between 0.60 and 0.7199)), 1,
if[cash_hand_player_statistics.flg_r_cbet and cash_hand_summary.cnt_players >= 6 and ((cash_hand_summary.amt_pot_r < 80 and (cash_hand_player_statistics.amt_r_bet_made / (cash_hand_summary.amt_pot_r * .95)) between 0.60 and 0.7199) or (cash_hand_summary.amt_pot_r >= 80 and (cash_hand_player_statistics.amt_r_bet_made / (cash_hand_summary.amt_pot_r - 4)) between 0.60 and 0.7199)), 1, 0]]]])


It works but I want to add another condition: the river cbet wasn't an allin bet. Is there a way to do that? Should I add: cash_hand_player_statistics.enum_allin = 'N' ?

[Flag_Hippo]

It works but I want to add another condition: the river cbet wasn't an allin bet. Is there a way to do that? Should I add: cash_hand_player_statistics.enum_allin = 'N' ?

That isn't suitable as the player may end up all-in with subsequent actions but you can compare the size of the bet with the players effective stack:

Code: Select all

cash_hand_player_statistics.amt_r_bet_made < cash_hand_player_statistics.amt_r_effective_stack

[ttesone]

It works but I want to add another condition: the river cbet wasn't an allin bet. Is there a way to do that? Should I add: cash_hand_player_statistics.enum_allin = 'N' ?

That isn't suitable as the player may end up all-in with subsequent actions but you can compare the size of the bet with the players effective stack:

Code: Select all

cash_hand_player_statistics.amt_r_bet_made < cash_hand_player_statistics.amt_r_effective_stack

That worked! Thanks!

Now I'd like to make a column to count hands for the following situation:
It's a 3BP, player is OOP, player did a cbet between 72% and 80% of the pot.

For the first 2 restrictions I used:
if[substring(cash_hand_summary.str_aggressors_p from 3 for 1)::int > cash_hand_player_statistics.position and char_length(cash_hand_summary.str_aggressors_p) = 3

But I'm not getting any hands returned and there are cases I looked for manually with a filter. Here's the complete column:

sum(if[substring(cash_hand_summary.str_aggressors_p from 3 for 1)::int > cash_hand_player_statistics.position and char_length(cash_hand_summary.str_aggressors_p) = 3 and cash_hand_player_statistics.flg_f_cbet and cash_hand_summary.cnt_players = 2 and ((cash_hand_summary.amt_pot_f < 20 and (cash_hand_player_statistics.amt_f_bet_made / (cash_hand_summary.amt_pot_f * .95)) between 0.72 and 0.8) or (cash_hand_summary.amt_pot_f >= 20 and (cash_hand_player_statistics.amt_f_bet_made / (cash_hand_summary.amt_pot_f - 1)) between 0.72 and 0.8)), 1,
if[substring(cash_hand_summary.str_aggressors_p from 3 for 1)::int > cash_hand_player_statistics.position and char_length(cash_hand_summary.str_aggressors_p) = 3 and cash_hand_player_statistics.flg_f_cbet and cash_hand_summary.cnt_players = 3 and ((cash_hand_summary.amt_pot_f < 40 and (cash_hand_player_statistics.amt_f_bet_made / (cash_hand_summary.amt_pot_f * .95)) between 0.72 and 0.8) or (cash_hand_summary.amt_pot_f >= 40 and (cash_hand_player_statistics.amt_f_bet_made / (cash_hand_summary.amt_pot_f - 2)) between 0.72 and 0.8)), 1,
if[substring(cash_hand_summary.str_aggressors_p from 3 for 1)::int > cash_hand_player_statistics.position and char_length(cash_hand_summary.str_aggressors_p) = 3 and cash_hand_player_statistics.flg_f_cbet and cash_hand_summary.cnt_players between 4 and 5 and ((cash_hand_summary.amt_pot_f < 60 and (cash_hand_player_statistics.amt_f_bet_made / (cash_hand_summary.amt_pot_f * .95)) between 0.72 and 0.8) or (cash_hand_summary.amt_pot_f >= 60 and (cash_hand_player_statistics.amt_f_bet_made / (cash_hand_summary.amt_pot_f - 3)) between 0.72 and 0.8)), 1,
if[substring(cash_hand_summary.str_aggressors_p from 3 for 1)::int > cash_hand_player_statistics.position and char_length(cash_hand_summary.str_aggressors_p) = 3 and cash_hand_player_statistics.flg_f_cbet and cash_hand_summary.cnt_players >= 6 and ((cash_hand_summary.amt_pot_f < 80 and (cash_hand_player_statistics.amt_f_bet_made / (cash_hand_summary.amt_pot_f * .95)) between 0.72 and 0.8) or (cash_hand_summary.amt_pot_f >= 80 and (cash_hand_player_statistics.amt_f_bet_made / (cash_hand_summary.amt_pot_f - 4)) between 0.72 and 0.8)), 1, 0]]]])

[ttesone]

Figured the OOP part wasn't working but I fixed it using the HUD filter "has position: oop"

[Flag_Hippo]

substring(cash_hand_summary.str_aggressors_p from 3 for 1)::int > cash_hand_player_statistics.position and char_length(cash_hand_summary.str_aggressors_p) = 3 and cash_hand_player_statistics.flg_f_cbet

This did not give any results because here you are comparing the position of the 3bettor to the position of the player the statistic is written for (also the 3bettor) and they would be equal to one another.