New stat

[Santa25]

I can't create statistics. i want to get the result. How often does SB fold to 3 bet from BB
(me_cnt_sb_fold_to_bb_3bet / me_cnt_sb_fold_to_bb_3bet_opp) * 100
format type percent
summary type same as format
me_cnt_sb_fold_to_bb_3bet columns sum(if[
tourney_hand_summary.str_aggressors_p LIKE '89%'
AND tourney_hand_summary.str_actors_p LIKE '9%'
AND tourney_hand_player_statistics.flg_p_3bet
AND tourney_hand_player_statistics.flg_p_fold
AND ((tourney_hand_player_statistics.amt_p_raise_facing / tourney_hand_player_statistics.amt_p_effective_stack) < 0.4),
1, 0])
me_cnt_sb_fold_to_bb_3bet_opp
sum(if[
tourney_hand_summary.str_aggressors_p LIKE '89%'
AND tourney_hand_summary.str_actors_p LIKE '9%'
AND tourney_hand_player_statistics.amt_p_2bet_facing > 0
AND ((tourney_hand_player_statistics.amt_p_raise_facing / tourney_hand_player_statistics.amt_p_effective_stack) < 0.4),
1, 0])

In my results it turns out to be 0. I check - there were definitely situations but still zero - I don't understand why, please tell me

[Flag_Hippo]

AND tourney_hand_player_statistics.flg_p_3bet

Anything using tourney_hand_player_statistics is specific to the player you are writing the statistic for and this says the player 3bet themselves which isn't what you want. Since you've also specified the SB is 2betting using the aggressors string you get a zero result since it's not possible for a player to 2bet and 3bet in the same hand. Finally tourney_hand_player_statistics.amt_p_raise_facing is the size of the last raise faced by the player so as this is for 3bets only I'd recommend using tourney_hand_player_statistics.amt_p_3bet_facing instead:

Code: Select all

sum(if[tourney_hand_player_statistics.position = 9 and tourney_hand_player_statistics.enum_p_3bet_action = 'F' AND tourney_hand_summary.str_aggressors_p LIKE '898%' AND tourney_hand_summary.str_actors_p LIKE '9%' AND ((tourney_hand_player_statistics.amt_p_3bet_facing / tourney_hand_player_statistics.amt_p_effective_stack) < 0.4), 1, 0])

Code: Select all

sum(if[tourney_hand_player_statistics.position = 9 and tourney_hand_player_statistics.enum_p_3bet_action SIMILAR TO '(F|C|R)' AND tourney_hand_summary.str_aggressors_p LIKE '898%' AND tourney_hand_summary.str_actors_p LIKE '9%' AND ((tourney_hand_player_statistics.amt_p_3bet_facing / tourney_hand_player_statistics.amt_p_effective_stack) < 0.4), 1, 0])

[Santa25]

AND tourney_hand_player_statistics.flg_p_3bet

Anything using tourney_hand_player_statistics is specific to the player you are writing the statistic for and this says the player 3bet themselves which isn't what you want. Since you've also specified the SB is 2betting using the aggressors string you get a zero result since it's not possible for a player to 2bet and 3bet in the same hand. Finally tourney_hand_player_statistics.amt_p_raise_facing is the size of the last raise faced by the player so as this is for 3bets only I'd recommend using tourney_hand_player_statistics.amt_p_3bet_facing instead:

Code: Select all

sum(if[tourney_hand_player_statistics.position = 9 and tourney_hand_player_statistics.enum_p_3bet_action = 'F' AND tourney_hand_summary.str_aggressors_p LIKE '898%' AND tourney_hand_summary.str_actors_p LIKE '9%' AND ((tourney_hand_player_statistics.amt_p_3bet_facing / tourney_hand_player_statistics.amt_p_effective_stack) < 0.4), 1, 0])

Code: Select all

sum(if[tourney_hand_player_statistics.position = 9 and tourney_hand_player_statistics.enum_p_3bet_action SIMILAR TO '(F|C|R)' AND tourney_hand_summary.str_aggressors_p LIKE '898%' AND tourney_hand_summary.str_actors_p LIKE '9%' AND ((tourney_hand_player_statistics.amt_p_3bet_facing / tourney_hand_player_statistics.amt_p_effective_stack) < 0.4), 1, 0])

Thank you so much, it helped me.

[Santa25]

I'm trying to create a new stat. Situational button fold - sb openraise - bb call - flop sb bet - bb cal - turn - sb check - bb bet sb fold - I want to see how often sb gives up on a bet on a turn

stat (me_cnt_sb_fold_to_turn_probe * 100.0) / NULLIF(me_cnt_sb_fold_to_turn_probe_opp, 0)
me_cnt_sb_fold_to_turn_probe * 100.0
SUM(
CASE
WHEN tourney_hand_player_statistics.position = 8
AND tourney_hand_summary.str_aggressors_p LIKE '89%'
AND tourney_hand_summary.str_actors_p LIKE '89%'
AND tourney_hand_player_statistics.flg_p_cbet_f
AND tourney_hand_player_statistics.flg_p_call_f
AND tourney_hand_player_statistics.flg_p_chk_t
AND tourney_hand_player_statistics.flg_p_fold_t
THEN 1
ELSE 0
END
)
me_cnt_sb_fold_to_turn_probe_opp
SUM(
CASE
WHEN tourney_hand_player_statistics.position = 8
AND tourney_hand_summary.str_aggressors_p LIKE '89%'
AND tourney_hand_summary.str_actors_p LIKE '89%'
AND tourney_hand_player_statistics.flg_p_cbet_f
AND tourney_hand_player_statistics.flg_p_call_f
AND tourney_hand_player_statistics.flg_p_chk_t
AND (
tourney_hand_player_statistics.flg_p_fold_t
OR tourney_hand_player_statistics.flg_p_call_t
OR tourney_hand_player_statistics.flg_p_raise_t
)
THEN 1
ELSE 0
END
)

I'm checking validate columns different options, there's an error everywhere= The statement is not valid SQL
I ran it through three neural networks - but all attempts were = The statement is not valid SQL

sum(if[
tourney_hand_player_statistics.position = 8
and tourney_hand_summary.str_aggressors_p LIKE '89%'
and tourney_hand_summary.str_actors_p LIKE '89%'
and tourney_hand_player_statistics.flg_p_cbet_f
and tourney_hand_player_statistics.flg_p_call_f
and tourney_hand_player_statistics.flg_p_chk_t
and tourney_hand_player_statistics.flg_p_fold_t,
1, 0])

sum(if[
tourney_hand_player_statistics.position = 8
and tourney_hand_summary.str_aggressors_p LIKE '89%'
and tourney_hand_summary.str_actors_p LIKE '89%'
and tourney_hand_player_statistics.flg_p_cbet_f
and tourney_hand_player_statistics.flg_p_call_f
and tourney_hand_player_statistics.flg_p_chk_t
and (tourney_hand_player_statistics.flg_p_fold_t
or tourney_hand_player_statistics.flg_p_call_t
or tourney_hand_player_statistics.flg_p_raise_t),
1, 0])

sum(if[
-- Preflop
tourney_hand_summary.str_aggressors_p LIKE '89%'
AND tourney_hand_summary.str_actors_p LIKE '8%'
AND tourney_hand_player_statistics.flg_p_open_opp
AND tourney_hand_player_statistics.flg_p_raise = 1
-- Flop
AND tourney_hand_player_statistics.flg_f_bet = 1
AND lookup_actions_f.action LIKE '%C'
-- Turn
AND tourney_hand_player_statistics.flg_t_check = 1
AND lookup_actions_t.action LIKE 'B%'
, 1, 0])

SELECT
lookup_actions_t.action,
tourney_hand_player_statistics.flg_t_fold
FROM tourney_hand_summary
JOIN tourney_hand_player_statistics
ON tourney_hand_summary.id_hand = tourney_hand_player_statistics.id_hand
WHERE tourney_hand_summary.str_aggressors_p LIKE '89%'
LIMIT 10;

sum(if[tourney_hand_summary.str_aggressors_p LIKE '89%' AND tourney_hand_summary.str_actors_p LIKE '8%' AND tourney_hand_player_statistics.flg_p_open_opp AND tourney_hand_player_statistics.flg_p_raise = 1 AND tourney_hand_player_statistics.flg_f_bet = 1 AND lookup_actions_f.action LIKE '%C' AND tourney_hand_player_statistics.flg_t_check = 1 AND lookup_actions_t.action LIKE 'B%' AND tourney_hand_player_statistics.flg_t_fold = 1, 1, 0])

sum(if[tourney_hand_summary.str_aggressors_p LIKE '89%' AND tourney_hand_summary.str_actors_p LIKE '8%' AND tourney_hand_player_statistics.flg_p_open_opp AND tourney_hand_player_statistics.flg_p_raise = 1 AND tourney_hand_player_statistics.flg_f_bet = 1 AND lookup_actions_f.action LIKE '%C' AND tourney_hand_player_statistics.flg_t_check = 1 AND lookup_actions_t.action LIKE 'B%', 1, 0])

sum(if[
tourney_hand_summary.str_aggressors_p LIKE '89%'
AND tourney_hand_summary.str_actors_p LIKE '8%'
AND tourney_hand_player_statistics.flg_p_open_opp
AND tourney_hand_player_statistics.flg_p_raise = 1
AND tourney_hand_player_statistics.flg_f_bet = 1
AND lookup_actions_f.action LIKE '%C'
AND tourney_hand_player_statistics.flg_t_check = 1
AND lookup_actions_t.action LIKE 'B%'
AND tourney_hand_player_statistics.flg_t_fold = 1,
1,
0
])

sum(if[
tourney_hand_summary.str_aggressors_p LIKE '89%'
AND tourney_hand_summary.str_actors_p LIKE '8%'
AND tourney_hand_player_statistics.flg_p_open_opp
AND tourney_hand_player_statistics.flg_p_raise = 1
AND tourney_hand_player_statistics.flg_f_bet = 1
AND lookup_actions_f.action LIKE '%C'
AND tourney_hand_player_statistics.flg_t_check = 1
AND lookup_actions_t.action LIKE 'B%',
1,
0
])

me_cnt_sb_fold_turn =
SUM(
IF[
tourney_hand_summary.str_aggressors_p = '9'
AND tourney_hand_player_statistics.position = 9
AND tourney_hand_player_statistics.enum_p_2bet_action = 'R'
AND tourney_hand_player_statistics.enum_p_3bet_action = 'C'
AND tourney_hand_player_statistics.enum_f_bet_action = 'B'
AND tourney_hand_player_statistics.enum_f_call_action = 'C'
AND tourney_hand_player_statistics.enum_t_check_action = 'K'
AND tourney_hand_player_statistics.enum_t_bet_action = 'B'
AND tourney_hand_player_statistics.enum_t_fold_action = 'F',
1, 0
]
)

me_cnt_sb_fold_turn_opp =
SUM(
IF[
tourney_hand_summary.str_aggressors_p = '9'
AND tourney_hand_player_statistics.position = 9
AND tourney_hand_player_statistics.enum_p_2bet_action = 'R'
AND tourney_hand_player_statistics.enum_p_3bet_action = 'C'
AND tourney_hand_player_statistics.enum_f_bet_action = 'B'
AND tourney_hand_player_statistics.enum_f_call_action = 'C'
AND tourney_hand_player_statistics.enum_t_check_action = 'K'
AND tourney_hand_player_statistics.enum_t_bet_action = 'B',
1, 0
]
)

me_cnt_sb_fold_turn =
SUM(
IF(
tourney_hand_summary.str_aggressors_p = '9'
AND tourney_hand_player_statistics.position = 9
AND tourney_hand_player_statistics.enum_p_2bet_action = 'R'
AND tourney_hand_player_statistics.enum_p_3bet_action = 'C'
AND tourney_hand_player_statistics.enum_f_bet_action = 'B'
AND tourney_hand_player_statistics.enum_f_call_action = 'C'
AND tourney_hand_player_statistics.enum_t_check_action = 'K'
AND tourney_hand_player_statistics.enum_t_bet_action = 'B'
AND tourney_hand_player_statistics.enum_t_fold_action = 'F',
1, 0
)
)

me_cnt_sb_fold_turn_opp =
SUM(
IF(
tourney_hand_summary.str_aggressors_p = '9'
AND tourney_hand_player_statistics.position = 9
AND tourney_hand_player_statistics.enum_p_2bet_action = 'R'
AND tourney_hand_player_statistics.enum_p_3bet_action = 'C'
AND tourney_hand_player_statistics.enum_f_bet_action = 'B'
AND tourney_hand_player_statistics.enum_f_call_action = 'C'
AND tourney_hand_player_statistics.enum_t_check_action = 'K'
AND tourney_hand_player_statistics.enum_t_bet_action = 'B',
1, 0
)
)

all situations =The statement is not valid SQL

I wrote this so that you understand that I have tried many times but without success

Please help me

I found a very short guide - maybe there is a more detailed one?

[Flag_Hippo]

There are multiple issues here including but not limited to:

Code: Select all

tourney_hand_player_statistics.flg_p_cbet_f
tourney_hand_player_statistics.flg_p_call_f
tourney_hand_player_statistics.flg_p_chk_t
tourney_hand_player_statistics.flg_p_fold_t
tourney_hand_player_statistics.flg_p_call_t
tourney_hand_player_statistics.flg_p_raise_t
tourney_hand_player_statistics.enum_p_2bet_action
tourney_hand_player_statistics.enum_f_bet_action
tourney_hand_player_statistics.enum_f_call_action
tourney_hand_player_statistics.enum_t_check_action
tourney_hand_player_statistics.enum_t_bet_action
tourney_hand_player_statistics.enum_t_fold_action

None of these exist in the PokerTracker 4 database schema.

Code: Select all

tourney_hand_player_statistics.flg_t_check = 1

Anything using flg is a boolean and is either true or false.

Code: Select all

tourney_hand_summary.str_aggressors_p = '9'

The aggressors string always starts with an '8'. This post has more information on how the actors and aggressors strings work.

Code: Select all

= 'K'

'K' isn't going to be a valid action on any street.

I wouldn't rely on AI to give you 100% correct expressions. You will either need create or review them yourself to make sure everything is valid. See this guide for the basics on custom statistics creation and this guide for a deeper walkthrough. While the latter was written for PokerTracker 3 and the user interface is different the techniques still apply to PokerTracker 4. Also while we have not published the schema like we did for PokerTracker 3 you can find the files used to create the database in your PokerTracker4 installation folder:

C:\Program Files (x86)\PokerTracker 4\Data\Schemas\schema.postgres.sql

The meaning of database fields with the same names haven't changed in any significant way from PokerTracker 3.

PokerTracker 3 Database Schema Documentation

[Santa25]

thanks for the last answer
I'm at a dead end - please help
I am very close to the truth
I'll repeat the task: I'm trying to create a new stat. Situation- button fold - sb openraise - bb call - flop sb bet - bb cal - turn - sb check - bb bet sb fold - I want to see how often sb gives up on a bet on a turn

I have two types of statistics working
1 where the formula (stats) A/ B) * 100
A sum(if[
tourney_hand_player_statistics.amt_t_bet_facing > 0
AND tourney_hand_player_statistics.flg_f_cbet
AND tourney_hand_player_statistics.flg_f_cbet_opp
AND tourney_hand_player_statistics.flg_t_check
AND tourney_hand_player_statistics.flg_t_fold
AND lookup_positions.flg_sb,
1, 0
])
B sum(if[
tourney_hand_player_statistics.amt_t_bet_facing > 0
AND tourney_hand_player_statistics.flg_f_cbet
AND tourney_hand_player_statistics.flg_f_cbet_opp
AND tourney_hand_player_statistics.flg_t_check
AND lookup_positions.flg_sb,
1, 0
])
or where the formula (stats) A/ B) * 100

A sum(if[
tourney_hand_player_statistics.amt_t_bet_facing > 0
AND tourney_hand_player_statistics.flg_f_cbet
AND tourney_hand_player_statistics.flg_f_cbet_opp
AND tourney_hand_player_statistics.flg_t_check
AND tourney_hand_player_statistics.flg_t_fold
AND lookup_positions.flg_sb
AND NOT tourney_hand_player_statistics.flg_p_3bet_def_opp,
1, 0])
B sum(if[
tourney_hand_player_statistics.amt_t_bet_facing > 0
AND tourney_hand_player_statistics.flg_f_cbet
AND tourney_hand_player_statistics.flg_f_cbet_opp
AND tourney_hand_player_statistics.flg_t_check
AND tourney_hand_player_statistics.flg_t_fold
AND lookup_positions.flg_sb
AND NOT tourney_hand_player_statistics.flg_p_3bet_def_opp,
1, 0])

Then I ran into a problem
for both situations when I check in the replay hand history - there remains a situation where preflop button openraise - sb reraise bb fold - button call . And then flop sb cbet - button call - turn sb check - button bet

So I can't get rid of 3 bet preflop, I tried adding AND NOT tourney_hand_player_statistics.flg_p_3bet_def_opp
AND NOT tourney_hand_player_statistics.flg_f_3bet_def_opp
AND NOT tourney_hand_player_statistics.flg_t_3bet_def_opp
AND NOT tourney_hand_player_statistics.flg_r_3bet_def_opp

I check everything on validate - everything passes

stat : Format type - percent
summary type : same as format

column summary type - sum . when clicking the checkbox on cache there is an error - therefore without a checkbox on cache

[Flag_Hippo]

I'll repeat the task: I'm trying to create a new stat. Situation- button fold - sb openraise - bb call - flop sb bet - bb cal - turn - sb check - bb bet sb fold - I want to see how often sb gives up on a bet on a turn

That can be done several ways. Here is an example:

Code: Select all

sum(if[tourney_hand_player_statistics.position = 9 AND tourney_hand_summary.str_aggressors_p = '89' AND tourney_hand_summary.str_actors_p = '98' AND lookup_actions_f.action = 'B' AND lookup_actions_t.action = 'XF', 1, 0])

Code: Select all

sum(if[tourney_hand_player_statistics.position = 9 AND tourney_hand_summary.str_aggressors_p = '89' AND tourney_hand_summary.str_actors_p = '98' AND lookup_actions_f.action = 'B' AND lookup_actions_t.action LIKE 'X_%', 1, 0])

This post has more information on how the actors and aggressors strings work if you want to test for different situations.

[Santa25]

I'll repeat the task: I'm trying to create a new stat. Situation- button fold - sb openraise - bb call - flop sb bet - bb cal - turn - sb check - bb bet sb fold - I want to see how often sb gives up on a bet on a turn

That can be done several ways. Here is an example:

Code: Select all

sum(if[tourney_hand_player_statistics.position = 9 AND tourney_hand_summary.str_aggressors_p = '89' AND tourney_hand_summary.str_actors_p = '98' AND lookup_actions_f.action = 'B' AND lookup_actions_t.action = 'XF', 1, 0])

Code: Select all

sum(if[tourney_hand_player_statistics.position = 9 AND tourney_hand_summary.str_aggressors_p = '89' AND tourney_hand_summary.str_actors_p = '98' AND lookup_actions_f.action = 'B' AND lookup_actions_t.action LIKE 'X_%', 1, 0])

This post has more information on how the actors and aggressors strings work if you want to test for different situations.

Thank you

BTN (Button) → position = 9
SB (Small Blind) → position = 8
BB (Big Blind) → position = 7

Is this correct?
does this change for a three-player game? in spin and go tournament

[Flag_Hippo]

BTN (Button) → position = 9
SB (Small Blind) → position = 8
BB (Big Blind) → position = 7

Is this correct?

BTN is position 0, SB is position 9 and BB is position 8.

does this change for a three-player game? in spin and go tournament

These position numbers do not change. If 4 players are dealt in then the CO is position 1 and so on - see this guide.