SQL query

[pelerin]

Not exactly report related but reports seemed the best section to ask this.

I'm doing a SQL query on the HEM database and have figured out most of the things I need but I can't see how to get AF by street for each player.

Thanks in advance.

[Rvg72]

This query will grab all postflop compiled stats for a specific player_ID

select CR.Player_ID,PFA.StreetActionType_ID,
sum(count) as Count,sum(betorraise) as BetOrRaise,sum(checks) as Checks,sum(CheckCall) as CheckCall,sum(CheckFold) as CheckFold,sum(CheckRaise) as CheckRaise,
sum(Bet) as Bet, sum(BetCall) as BetCall,sum(BetRaise) as BetRaise,sum(BetFold) as BetFold,sum(Raise) as Raise,sum(RaiseCall) as RaiseCall,
sum(RaiseFold) as RaiseFold,sum(RaiseRaise) as RaiseRaise,sum(call) as Call,sum(CallCall) as CallCall, sum(CallFold) as CallFold, sum(CallRaise) as CallRaise,
sum(fold) as Fold,sum(UnopenedInPosition) as UnopenedInPosition,sum(BetUnopenedInPosition) as BetUnopenedInPosition,sum(totalBets) as TotalBets,
sum(TotalCalls) as TotalCalls,sum(sawShowdown) as SawShowdown,sum(WonShowdown) as WonShowdown
from CompiledResults_month CR, PostFlopActions_month PFA
where CR.Player_ID = 147 and CR.CompiledPlayerResults_ID = PFA.CompiledPlayerResults_ID and CR.BBGroup_ID = 0 and CR.NumberOfPlayers between 2 and 10 and CR.PlayedonMonth between 200001 and 200808 group by CR.Player_ID,PFA.StreetActionType_ID order by player_ID,StreetActionType_ID

Now, you will see a bunch of "streetactiontype_ID"'s. 0 = overall, 1 = flop, 2 = turn, 3 = river

For Aggression Factor you want TotalBets/TotalCalls

Here is a list of all street action types starting with 0 - the ones starting with "postflop" are all obsolete now and a future update will add many more.

overall,flopoverall,turnoverall,riveroverall,flopl impedflop,flopaspfr,flopvspfr,flopas3Bet,flopvs3Be t,flopas4Bet,flopvs4Bet,flopcalltworaisers,floprai setworaisers,floplimpreraised,
postfloplimpedflop, postflopaspfr, postflopvspfr, postflopas3Bet, postflopvs3Bet, postflopas4Bet, postflopvs4Bet, postflopcalltworaisers, postflopraisetworaisers, postfloplimpreraised,
couldCBetFlop,couldCBetTurn,couldCBetRiver,facingC BetFlop,facingCBetTurn,facingCBetRiver,
turnAfterFlopCheckRaise,riverAfterTurnCheckRaise,t urnAfterFlopRaise,riverAfterTurnRaise,
TurnAfterSkippingFlopCbetIP,RiverAfterSkippingTurn CBetIP,
flopIPvsSkippedCBet,turnIPvsSkippedCBet,riverIPvsS kippedCBet,turnOOPvsSkippedFlopCbet,riverOOPvsSkip pedTurnCBet,
preflopAfterLimp

[pelerin]

That's great, thank you.

[weaktight]

How do I get the vpip, pfr and 3bet% via query?

I just use didvpip and didpfr from playerhandscashkeycolumns? What about 3bet%? Is it based on firstpreflopactiontype_id somehow?

Is CompiledResults_month always up to date? I noticed some of my compiled tables were empty.

Thanks.

[weaktight]

Is there anyway to filter this based on hands from a specific table?

[Rvg72]

How do I get the vpip, pfr and 3bet% via query?

I just use didvpip and didpfr from playerhandscashkeycolumns? What about 3bet%? Is it based on firstpreflopactiontype_id somehow?

Is CompiledResults_month always up to date? I noticed some of my compiled tables were empty.

Thanks.

Fastest way to get vpip/pfr type stats is to use the compiledplayerresults_month table - only the _month tables are still in use as far as compiled stats are concerned.

The best way to see how stats are queried is to close HM and edit the holdemmanager.config and set LogDatabaseCommands = True and then relaunch and start running reports or different views. After each query you will see a dbtrace.log file being updated with the actual query that was generated.

Roy

[_Hannes_]

Hello and thanks for this great piece of software.

I need a little bit help with the query above. When i use the following query

select
sum(CR.totalBets) as TotalBets,
sum(CR.TotalCalls) as TotalCalls
from CompiledPlayerResults_month CR, players PL
where PL.Player_ID = 12

i recieve TotalBets=636 and TotalCalls=283 which results in AF=2.25. When i look at the Agg that is displayed in HM for that player it shows 2.56. Where is my error?

[Rvg72]

I don't think that query will work, try

select sum(totalbets) as totalbets, sum(totalcalls) as totalcalls
from postflopactions_month where compiledplayerresults_id in
(select
compiledplayerresults_id from compiledresults_month
where Player_ID = 12 ) and streetactiontype_id = 0

[ilovewta]

Can anyone post SQL query for getting preflop VPIP, PFR and AGG in particular position (i.e. button, SB, BB, UTG, cutoff)?

Thanks in advance.

[_Hannes_]

Thanks a lot for your answer Rvg72. With your help and the above query i was able to get almost all stats i need.

[Youretoast]

I pulled this up from the archives. If I run this query limited to streetactiontype_id = 3, what is the calculation for the percentage of times this player folded the river? fold / count does not come up with the accurate number.

Anyone have any ideas?

[toxa220]

How do I get the DoncBet, RaiseCB and FoldToCB via query?
Thanks.

[shaddownight]

I see earlier in this thread a list of streetactiontype_id's. I have summed them up below to make them more clearer. Have I got the numbering correct? Also there appears to no be more streetactiontype_id's. Does anyone have a more complete up-to-date list?

Thx

0 - overall
1 - flopoverall
2 - turnoverall
3 - riveroverall
4 - floplimpedflop
5 - flopaspfr
6 - flopvspfr
7 - flopas3Bet
8 - flopvs3Bet
9 - flopas4Bet
10 - flopvs4Bet
11 - flopcalltworaisers
12 - flopraisetworaisers
13 - floplimpreraised
14 - postfloplimpedflop
15 - postflopaspfr
16 - postflopvspfr
17 - postflopas3Bet
18 - postflopvs3Bet
19 - postflopas4Bet
20 - postflopvs4Bet
21 - postflopcalltworaisers
22 - postflopraisetworaisers
23 - postfloplimpreraised
24 - couldCBetFlop
25 - couldCBetTurn
26 - couldCBetRiver
27 - facingCBetFlop
28 - facingCBetTurn,
29 - facingCBetRiver
30 - turnAfterFlopCheckRaise
31 - riverAfterTurnCheckRaise
32 - turnAfterFlopRaise
33 - riverAfterTurnRaise
34 - TurnAfterSkippingFlopCbetIP
35 - RiverAfterSkippingTurn CBetIP
36 - flopIPvsSkippedCBet
37 - turnIPvsSkippedCBet
38 - riverIPvsSkippedCBet
39 - turnOOPvsSkippedFlopCbet
40 - riverOOPvsSkippedTurnCBet
41 - preflopAfterLimp