Your Average Stake In HEM

I wanted to find out the average stake I played in 2010, but this information doesn’t seem to be readily available in Hold’em Manager. You can do it manually by doing a report by 'stakes’, then exporting the data to CSV, firing it up in Excel then working it out, but this is a bit of a chore so I thought I’d write a short SQL script to pull it out of the database.

You can execute the below in pgadmin against your HEM database. Change the text 'Hood’ to the name of the alias you use in Hold’em Manager (if you don’t use an alias, you can change the code a little to just run against one player ID; or you can create an alias with just one player in).

The dates are set for 2010, but you can easily tweak these to query between different dates.

It handles multiple currencies by querying the currency rates in the DB and converting to USD (it doesn’t calculate the average blind at the correct currency rate at the time each individual hand was played).

The result is your average big blind for the year, in USD. My result spits out $4.8 – so my average big blind was just under $5 (i.e. 5/10).

select sum(handweight * totalhands) / sum(totalhands) from (
select gt.gametypedescription as "gametype",
count(ph.*) as totalhands,
gt.bigblind * c.current_rate_in_usd / 100 as "handweight"
from playerhandscashkeycolumns_hero ph 
join players pl on (pl.player_id = ph.player_id) 
join pokerhands_hero pkh on pkh.pokerhand_id = ph.pokerhand_id 
join gametypes gt on gt.gametype_id = ph.gametype_id  
join currency c on gt.currency_id = c.currency_id
where ph.player_id in ((select a.player_id from aliases a join players p on a.aliasplayer_id = p.player_id where p.playername = 'Hood')) and 
ph.handtimestamp between to_timestamp('01/01/2010 00:00:00','mm/dd/yyyy hh24:mi:ss') and to_timestamp('01/01/2011 00:00:00','mm/dd/yyyy hh24:mi:ss')  and
gt.pokergame = 1  
group by "gametype", gt.bigblind, "handweight" order by bigblind desc
) as aggdata