Filter hand reports by player stats

[DeepMig]

Hello,

I want to analyse hands from players who meet a certain condition for a particular player stat.
Example: i want to list all the hands that contain a 3-bet from players who 3-bet less than 10% of the time.
I tried creating a custom report in the section 'Holdem Tournament Hand' then use the filter 3-bet preflop.
Then I wanted to add a custom filter like '3Bet Pre3flop < 10', but this stat is not available because it belongs to the Player's section.
Is there any way do achieve this, either with reports or another method?

Thanks for your time,
Miguel

[kraada]

Yes, it's just quite complicated.

For example, this filters for hands with players who have VP$IP between 15 and 20:


player.id_player in (SELECT player.id_player from player, holdem_hand_player_statistics where player.id_player = holdem_hand_player_statistics.id_player GROUP BY player.id_player HAVING (((( (CASE WHEN ( (sum((case when(holdem_hand_player_statistics.id_hand > 0) then 1 else 0 end)))) <> 0 THEN (((sum((case when(holdem_hand_player_statistics.flg_vpip) then 1 else 0 end))) * 1.0 )/( (sum((case when(holdem_hand_player_statistics.id_hand > 0) then 1 else 0 end))))) ELSE 0 END) ) * 100) BETWEEN 15 AND 20)))

You use this text in the blue Filters link in the Reports tab and it will work on any holdem cash game section.

[DeepMig]

If it's not asking too much, could you post how the same filter would look for tournament hands instead of cash?
Thanks.

[kraada]

Change "holdem_hand_player_statistics" to "tourney_holdem_hand_player_statistics" and that will make it for tournaments; nothing else is required.

[DeepMig]

Thanks a lot kraada.

I made a 3bet preflop filter and it looks like this:

player.id_player in (SELECT player.id_player from player, tourney_holdem_hand_player_statistics where player.id_player = tourney_holdem_hand_player_statistics.id_player GROUP BY player.id_player HAVING (((( (CASE WHEN ( (sum((case when(tourney_holdem_hand_player_statistics.flg_p_3bet_opp) then 1 else 0 end)))) <> 0 THEN (((sum((case when(tourney_holdem_hand_player_statistics.flg_p_3bet) then 1 else 0 end))) * 1.0 )/( (sum((case when(tourney_holdem_hand_player_statistics.flg_p_3bet_opp) then 1 else 0 end))))) ELSE 0 END) ) * 100) BETWEEN 30 AND 50)))

It's working but it gives me ALL the hands from players who 3bet between 30% and 50%.
Now I want to list only hands containing a 3bet from those players.
I tried adding a simple filter, but it didn't work.
Do I need a few more lines to the filter code?

[kraada]

You actually need to change the structure some. You want it to look like this for that kind of analysis:

tourney_holdem_hand_player_statistics.id_hand in (SELECT player.id_player from player, tourney_holdem_hand_player_statistics where tourney_holdem_hand_player_statistics.flg_p_3bet and player.id_player = tourney_holdem_hand_player_statistics.id_player GROUP BY player.id_player HAVING (((( (CASE WHEN ( (sum((case when(tourney_holdem_hand_player_statistics.flg_p_3bet_opp) then 1 else 0 end)))) <> 0 THEN (((sum((case when(tourney_holdem_hand_player_statistics.flg_p_3bet) then 1 else 0 end))) * 1.0 )/( (sum((case when(tourney_holdem_hand_player_statistics.flg_p_3bet_opp) then 1 else 0 end))))) ELSE 0 END) ) * 100) BETWEEN 30 AND 50)))

The changes here: We're now filtering for individual hands, not individual players (hence getting only hands where these players did some action), and we added tourney_holdem_hand_player_statistics.flg_p_3bet to the WHERE clause, so now we're only looking at hands where these specific players also made a 3bet.

[DeepMig]

I understand your logic and it sounds correct, but for some reason the filter it's not working.
I tried to make some changes, but couldn't get it to work.
Did you test it yourself?

[kraada]

I missed one small additional change:

tourney_holdem_hand_player_statistics.id_hand in (SELECT tourney_holdem_hand_player_statistics.id_hand from player, tourney_holdem_hand_player_statistics where tourney_holdem_hand_player_statistics.flg_p_3bet and player.id_player = tourney_holdem_hand_player_statistics.id_player GROUP BY player.id_player HAVING (((( (CASE WHEN ( (sum((case when(tourney_holdem_hand_player_statistics.flg_p_3bet_opp) then 1 else 0 end)))) <> 0 THEN (((sum((case when(tourney_holdem_hand_player_statistics.flg_p_3bet) then 1 else 0 end))) * 1.0 )/( (sum((case when(tourney_holdem_hand_player_statistics.flg_p_3bet_opp) then 1 else 0 end))))) ELSE 0 END) ) * 100) BETWEEN 30 AND 50)))