Custom stat - top set vs running full

[4StarGen]

Coding the flopped set and lost is easy, but how can I code the spot below?
PokerStars - €0.25 NL (6 max) - Holdem - 4 players
Hand converted by PokerTracker 4

BB: 198.48 BB (VPIP: 31.96, PFR: 25.82, 3Bet Preflop: 10.22, Hands: 602)
Hero (CO): 119.32 BB
BTN: 59.04 BB (VPIP: 35.23, PFR: 14.44, 3Bet Preflop: 7.01, Hands: 373)
SB: 213.44 BB (VPIP: 36.67, PFR: 10.00, 3Bet Preflop: 0.00, Hands: 30)

SB posts SB 0.4 BB, BB posts BB 1 BB

Pre Flop: (pot: 1.4 BB) Hero has T T

Hero raises to 2.48 BB, fold, SB calls 2.08 BB, fold

Flop: (5.96 BB, 2 players) J T 7
SB checks, Hero bets 4.24 BB, SB calls 4.24 BB

Turn: (14.44 BB, 2 players) Q
SB checks, Hero bets 10.28 BB, SB calls 10.28 BB

River: (35 BB, 2 players) Q
SB checks, Hero bets 24.8 BB, SB raises to 57.6 BB, Hero calls 32.8 BB

Spoiler: show

SB shows J Q (Full House, Queens full of Jacks)
(Pre 44%, Flop 6%, Turn 9%)
Hero mucks T T (Full House, Tens full of Queens)
(Pre 56%, Flop 94%, Turn 91%)
SB wins 144.2 BB
Rake paid 6 BB

[Flag_Hippo]

Try adding this subquery:

Code: Select all

cash_hand_player_statistics.id_hand in (SELECT chps.id_hand from cash_hand_player_statistics chps, cash_hand_player_combinations chpc where chps.id_player = chpc.id_player and chps.id_hand = chpc.id_hand and chps.flg_won_hand and chps.flg_showdown and chpc.flg_f_1pair and chpc.id_f_hand_strength = 3)

[4StarGen]

I'm trying to build a stat that shows me the times I flopped trips and villain flopped a full house... but my statement is invalid

Code: Select all

sum(if[((((cash_hand_player_combinations.flg_f_threeoak
               AND cash_hand_player_combinations.val_f_hole_cards_used = 1
               AND cash_hand_player_combinations.id_f_hand_strength BETWEEN 1 AND 3)))  and exists
  (select 1
   from cash_hand_player_statistics villain
   where villain.id_hand = cash_hand_player_statistics.id_hand
   AND NOT villain.flg_hero AND combo.flg_f_fullhouse), 1, 0]))

[Flag_Hippo]

Code: Select all

sum(CASE WHEN (cash_hand_player_combinations.flg_f_threeoak AND cash_hand_player_combinations.val_f_hole_cards_used = 1 AND cash_hand_player_combinations.id_f_hand_strength BETWEEN 1 AND 3) and cash_hand_player_statistics.id_hand in (SELECT chps.id_hand from cash_hand_player_statistics chps, cash_hand_player_combinations chpc where chps.id_player = chpc.id_player and chps.id_hand = chpc.id_hand and NOT chps.flg_hero and chpc.flg_f_fullhouse) THEN 1 ELSE 0 END)