If you have no interest in things so geeky as DIY database queries and obscure poker statistics, look away now. Next time I'll probably post a picture of one of my socks or something to bring a little balance back into the world.
So then, how many times have I flopped a set, and how much did I win? From just over 50,000 hands in my database now, we'd expect to see about 3000 pocket pairs dealt (1 in every 17 hands). The odds of flopping a set are are 7.5-1, so somewhere in the region of 350 flopped sets would be about right. I had no idea what to expect as a win rate for these hands alone.
If you could care less about SQL, just skip past the code section, otherwise here's what I did. I failed to find any more elegant way of checking for a flopped set than simply comparing the card rank of one of the hole cards with each card on the flop in turn. This query will actually count flopped quads as well as flopped sets, if I have any.
select count(*) as freq,
sum(gp.total_won) - sum(gp.total_bet) as won
from game_players gp, game g
where gp.player_id = 11
and gp.game_id = g.game_id
and gp.pair_hand = 1
and substring(gp.hole_card_1 from 1 for 1) in (
substring(g.flop_1 from 1 for 1),
substring(g.flop_2 from 1 for 1),
substring(g.flop_3 from 1 for 1))
Let the results speak. I've seen 317 flopped sets (close enough to what I was expecting) and overall made $2884.50 profit from those hands. That's just about $9.10 - 9 big bets at 50NL - each time. Look at it another way: just over every five flopped sets, I'm winning a full $50 stack from another player. That sounds OK.
Look at it yet another way: compare to pocket aces. Poker Tracker tells me that I've been dealt AA 253 times and that I won an average of 4.86 big bets each time. Three hundred of anything is no huge sample size, but it's a start in the right direction. We could say that flopped sets are, roughly, twice as profitable as pocket aces. That's definitely nothing to complain about.
The difference, of course, is that pocket aces always start off as the best hand - and by a long way too. What's been killing me lately is the abundance of set-over-set action, with me on the wrong end of it. I'm sure I'm remembering things worse than they actually were, so what I want to find out is both how and when I've been getting beat.
Adding the condition
and gp.total_won < gp.total_bet
to the end of the query will find only those hands where I flopped a set and did not win. This includes split-pot situations, as the rake taken from the pot will make the amount returned less than the amount I put in the pot. I used this condition to find only losses (no splits), although it assumes there are two players at showdown. Not strictly true, but close enough, and do it any more accurately would create a monster query.
and gp.total_won < gp.total_bet - (g.rake / 2)
The results: 32 losses, 8 splits. 87% of my flopped sets won at showdown (compare: I won with 89% of my pocket aces). I can live with that - there's still two cards to come, after all. The combined loss on those 32 hands was $984.15, or $30.75 per hand. A big chunk for sure, but almost certainly I was ahead when my money went in. If only there was an easy way to check that...
The best I've come up with is to find all my set-over-set confrontations. To do this I have to find the times both I and my opponent have a pair that matches one of the flop cards. The query looks a little something like this.
select me.hole_cards, opp.hole_cards,
me.total_won - me.total_bet as won
from game_players me, game_players opp, game g
where me.player_id = 11
and me.game_id = g.game_id
and me.pair_hand = 1
and substring(me.hole_card_1 from 1 for 1) in (
substring(g.flop_1 from 1 for 1),
substring(g.flop_2 from 1 for 1),
substring(g.flop_3 from 1 for 1))
and opp.player_id != me.player_id
and opp.game_id = g.game_id
and opp.pair_hand = 1
and substring(opp.hole_card_1 from 1 for 1) in (
substring(g.flop_1 from 1 for 1),
substring(g.flop_2 from 1 for 1),
substring(g.flop_3 from 1 for 1))
It's ugly for sure, and still a little flawed too. I can't even start to get my head around how this might cope with three players all flopping sets at the same time. This query just dumps out a list of every time I was in a pot with a flopped set against another flopped set, rather than counting wins and losses - or times I flopped the bigger set or smaller set. It's only a small sample size - 15 confrontations - but it proves a point.
Won 3, lost 12. Overall, I'm down $386 from those hands. A loss of $32.17 each time I got shafted is understandable, but I'm down an average of $25.70 - nearly 26 BB/hand - across all set over set scenarios, simply because I never seem to be on the right end of them. I would expect that these are usually breakeven situations over the long term: the times you're ahead you expect to get paid off, and the times you're behind you're almost certainly going to get stacked.
Boy I am owed. And if I talked to you about how poker's been going for me lately and spouted some far fetched story about how bad I'm running... just look. Told you so!
