Using Stat Formulas - SQL Query

[consideratio]

Hi!

I'm trying to use information provided in the "HM Stat Formulas" thread to access the db directly.

I'm happy to see the examples provided in the thread, such as:
< Stat GroupName="Default" ColumnName="VPIP" ValueExpressions="sum(case when didvpip = true then 1 else 0 end) as VPIPHands; sum(case when ph.preflopaction_id <> -1 then 1 else 0 end) as TotalPlayableHands" Evaluate="VPIPHands*100.0/TotalPlayableHands" ColumnHeader="VPIP%" ColumnFormat="0.0" ColumnWidth="61" Tooltip="% of hands player voluntarily put money in" / >

---

I seek guidence of how to create the query expressions to yield the same stats that one can add to the Hud --HUD Options->Player Preferences->Stats-tab--, for example the "BTN Call Open" stat.

I'm not yet comprehending how the querying of the HUD-stats relate to the formulas presented in the locked thread.

Any reply, hint or example is highly appreciated!

---

PS:
The stats i actually need that i know now of is:

Positional Call Open (BTN Call Open)
Positional 3bet (BTN 3-Bet) + total 3-bet
Positional Squeeze (BTN squeeze) + total squeeze

[consideratio]

I finally found the SQL SELECT statements that I think is the basis of what the HUD uses to determine stats like "SB Call Open", these SELECT statements was executed after/when an autoimport was run (importing an 'active' handhistory while the HUD was running).

So to get for example "SB Call Open" I figure one need to use these values and create some simpler expressions. Although... these expressions might not be so simple to figure out.

Is there a way to see the expression used by HEM to calculate "SB Call Open" for example?

---

Following below, the four SQL SELECT Queries sent to the DB directly after a autoimport - my guess is that these are to be used as foundation to calculate statistics used in the HUD.

[consideratio]

select
CR.Player_ID,

sum(earlyuohands) as EarlyUOHands,
sum(earlyuovpip) as EarlyUOVPIP,
sum(earlyuopfr) as EarlyUOPFR,
sum(middleuohands) as middleUOHands,
sum(middleuovpip) as middleUOVPIP,
sum(middleuopfr) as middleUOPFR,
sum(cutoffuohands) as cutoffUOHands,
sum(cutoffuovpip) as cutoffUOVPIP,
sum(cutoffuopfr) as cutoffUOPFR,
sum(buttonuohands) as buttonUOHands,
sum(buttonuovpip) as buttonUOVPIP,
sum(buttonuopfr) as buttonUOPFR,
sum(smallblinduohands) as smallblindUOHands,
sum(smallblinduovpip) as smallblindUOVPIP,
sum(smallblinduopfr) as smallblindUOPFR,
sum(bigblinduohands) as bigblindUOHands,
sum(bigblinduovpip) as bigblindUOVPIP,
sum(bigblinduopfr) as bigblindUOPFR,

sum(early1limperhands) as Early1limperHands,
sum(early1limpervpip) as Early1limperVPIP,
sum(early1limperpfr) as Early1limperPFR,
sum(middle1limperhands) as middle1limperHands,
sum(middle1limpervpip) as middle1limperVPIP,
sum(middle1limperpfr) as middle1limperPFR,
sum(cutoff1limperhands) as cutoff1limperHands,
sum(cutoff1limpervpip) as cutoff1limperVPIP,
sum(cutoff1limperpfr) as cutoff1limperPFR,
sum(button1limperhands) as button1limperHands,
sum(button1limpervpip) as button1limperVPIP,
sum(button1limperpfr) as button1limperPFR,
sum(smallblind1limperhands) as smallblind1limperHands,
sum(smallblind1limpervpip) as smallblind1limperVPIP,
sum(smallblind1limperpfr) as smallblind1limperPFR,
sum(bigblind1limperhands) as bigblind1limperHands,
sum(bigblind1limpervpip) as bigblind1limperVPIP,
sum(bigblind1limperpfr) as bigblind1limperPFR,

sum(early2ormorelimpershands) as Early2ormorelimpersHands,
sum(early2ormorelimpersvpip) as Early2ormorelimpersVPIP,
sum(early2ormorelimperspfr) as Early2ormorelimpersPFR,
sum(middle2ormorelimpershands) as middle2ormorelimpersHands,
sum(middle2ormorelimpersvpip) as middle2ormorelimpersVPIP,
sum(middle2ormorelimperspfr) as middle2ormorelimpersPFR,
sum(cutoff2ormorelimpershands) as cutoff2ormorelimpersHands,
sum(cutoff2ormorelimpersvpip) as cutoff2ormorelimpersVPIP,
sum(cutoff2ormorelimperspfr) as cutoff2ormorelimpersPFR,
sum(button2ormorelimpershands) as button2ormorelimpersHands,
sum(button2ormorelimpersvpip) as button2ormorelimpersVPIP,
sum(button2ormorelimperspfr) as button2ormorelimpersPFR,
sum(smallblind2ormorelimpershands) as smallblind2ormorelimpersHands,
sum(smallblind2ormorelimpersvpip) as smallblind2ormorelimpersVPIP,
sum(smallblind2ormorelimperspfr) as smallblind2ormorelimpersPFR,
sum(bigblind2ormorelimpershands) as bigblind2ormorelimpersHands,
sum(bigblind2ormorelimpersvpip) as bigblind2ormorelimpersVPIP,
sum(bigblind2ormorelimperspfr) as bigblind2ormorelimpersPFR,

sum(early1raiserhands) as Early1raiserHands,
sum(early1raiservpip) as Early1raiserVPIP,
sum(early1raiserpfr) as Early1raiserPFR,
sum(middle1raiserhands) as middle1raiserHands,
sum(middle1raiservpip) as middle1raiserVPIP,
sum(middle1raiserpfr) as middle1raiserPFR,
sum(cutoff1raiserhands) as cutoff1raiserHands,
sum(cutoff1raiservpip) as cutoff1raiserVPIP,
sum(cutoff1raiserpfr) as cutoff1raiserPFR,
sum(button1raiserhands) as button1raiserHands,
sum(button1raiservpip) as button1raiserVPIP,
sum(button1raiserpfr) as button1raiserPFR,
sum(smallblind1raiserhands) as smallblind1raiserHands,
sum(smallblind1raiservpip) as smallblind1raiserVPIP,
sum(smallblind1raiserpfr) as smallblind1raiserPFR,
sum(bigblind1raiserhands) as bigblind1raiserHands,
sum(bigblind1raiservpip) as bigblind1raiserVPIP,
sum(bigblind1raiserpfr) as bigblind1raiserPFR,

sum(early1raiserpluscallerhands) as Early1raiserpluscallerHands,
sum(early1raiserpluscallervpip) as Early1raiserpluscallerVPIP,
sum(early1raiserpluscallerpfr) as Early1raiserpluscallerPFR,
sum(middle1raiserpluscallerhands) as middle1raiserpluscallerHands,
sum(middle1raiserpluscallervpip) as middle1raiserpluscallerVPIP,
sum(middle1raiserpluscallerpfr) as middle1raiserpluscallerPFR,
sum(cutoff1raiserpluscallerhands) as cutoff1raiserpluscallerHands,
sum(cutoff1raiserpluscallervpip) as cutoff1raiserpluscallerVPIP,
sum(cutoff1raiserpluscallerpfr) as cutoff1raiserpluscallerPFR,
sum(button1raiserpluscallerhands) as button1raiserpluscallerHands,
sum(button1raiserpluscallervpip) as button1raiserpluscallerVPIP,
sum(button1raiserpluscallerpfr) as button1raiserpluscallerPFR,
sum(smallblind1raiserpluscallerhands) as smallblind1raiserpluscallerHands,
sum(smallblind1raiserpluscallervpip) as smallblind1raiserpluscallerVPIP,
sum(smallblind1raiserpluscallerpfr) as smallblind1raiserpluscallerPFR,
sum(bigblind1raiserpluscallerhands) as bigblind1raiserpluscallerHands,
sum(bigblind1raiserpluscallervpip) as bigblind1raiserpluscallerVPIP,
sum(bigblind1raiserpluscallerpfr) as bigblind1raiserpluscallerPFR,

sum(early2raisershands) as Early2raisersHands,
sum(early2raisersvpip) as Early2raisersVPIP,
sum(early2raiserspfr) as Early2raisersPFR,
sum(middle2raisershands) as middle2raisersHands,
sum(middle2raisersvpip) as middle2raisersVPIP,
sum(middle2raiserspfr) as middle2raisersPFR,
sum(cutoff2raisershands) as cutoff2raisersHands,
sum(cutoff2raisersvpip) as cutoff2raisersVPIP,
sum(cutoff2raiserspfr) as cutoff2raisersPFR,
sum(button2raisershands) as button2raisersHands,
sum(button2raisersvpip) as button2raisersVPIP,
sum(button2raiserspfr) as button2raisersPFR,
sum(smallblind2raisershands) as smallblind2raisersHands,
sum(smallblind2raisersvpip) as smallblind2raisersVPIP,
sum(smallblind2raiserspfr) as smallblind2raisersPFR,
sum(bigblind2raisershands) as bigblind2raisersHands,
sum(bigblind2raisersvpip) as bigblind2raisersVPIP,
sum(bigblind2raiserspfr) as bigblind2raisersPFR

from
CompiledResults_month CR,
CompiledPlayerResultsPositionAction_month CPR

where
CR.Player_ID in (123,16009,7076,16011,11785)
and CR.CompiledPlayerResults_ID = CPR.CompiledPlayerResults_ID
and CR.BBGroup_ID = 0
and CR.GameType_ID in (
Select GameType_ID
from GameTypes
where PokerGameType < 2 and PokerGame = 1
)
and CR.playedonmonth >= 200906
and CR.NumberOfPlayers Between 3 and 5

group by
CR.Player_ID

[consideratio]

select
CR.Player_ID,

sum(totalhands) as TotalHands,
sum(totalamountwon)/100.0 as TotalAmountWon,
sum(TotalRake)/100.0 as TotalRake,
sum(TotalBBsWon) as TotalBBsWon,
sum(vpiphands) as VPIPHands,
sum(pfrhands) as PFRHands,

sum(CouldColdCall) as CouldColdCall,
sum(DidColdCall) as DidColdCall,

sum(CouldThreeBet) as CouldThreeBet,
sum(DidThreeBet) as DidThreeBet,

sum(couldsqueeze) as CouldSqueeze,
sum(didsqueeze) as DidSqueeze,

sum(facingtwopreflopraisers) as FacingTwoPreflopRaisers,
sum(calledtwopreflopraisers) as CalledTwoPreflopRaisers,
sum(raisedtwopreflopraisers) as RaisedTwoPreflopRaisers,

sum(smallblindstealattempted) as SmallBlindStealAttempted,
sum(smallblindstealdefended) as SmallBlindStealDefended,
sum(smallblindstealreraised) as SmallBlindStealReraised,
sum(bigblindstealattempted) as BigBlindStealAttempted,
sum(bigblindstealdefended) as BigBlindStealDefended,
sum(bigblindstealreraised) as BigBlindStealReraised,

sum(sawnonsmallshowdown) as SawNonSmallShowdown,
sum(wonnonsmallshowdown) as WonNonSmallShowdown,
sum(WonHand) as WonHand,
sum(WonHandWhenSawFlop) as WonHandWhenSawFlop,
sum(facedthreebetpreflop) as FacedThreeBetPreflop,
sum(foldedtothreebetpreflop) as FoldedToThreeBetPreflop,
sum(calledthreebetpreflop) as CalledThreeBetPreflop,
sum(raisedthreebetpreflop) as RaisedThreeBetPreflop,
sum(facedfourbetpreflop) as FacedfourBetPreflop,
sum(foldedtofourbetpreflop) as FoldedTofourBetPreflop,
sum(calledfourbetpreflop) as CalledfourBetPreflop,
sum(raisedfourbetpreflop) as RaisedfourBetPreflop,
sum(SawSHowdown) as SawShowdown,
sum(WonShowdown) as WonShowdown,

sum(flopcontinuationbetpossible) as FlopContinuationBetPossible,
sum(flopcontinuationbetmade) as FlopContinuationBetMade,
sum(turncontinuationbetpossible) as TurnContinuationBetPossible,
sum(turncontinuationbetmade) as TurnContinuationBetMade,
sum(rivercontinuationbetpossible) as RiverContinuationBetPossible,
sum(rivercontinuationbetmade) as RiverContinuationBetMade,
sum(facingflopcontinuationbet) as FacingFlopContinuationBet,
sum(calledflopcontinuationbet) as CalledFlopContinuationBet,
sum(foldedtoflopcontinuationbet) as FoldedToFlopContinuationBet,
sum(raisedflopcontinuationbet) as RaisedFlopContinuationBet,
sum(facingTurncontinuationbet) as FacingTurnContinuationBet,
sum(calledTurncontinuationbet) as CalledTurnContinuationBet,
sum(foldedtoTurncontinuationbet) as FoldedToTurnContinuationBet,
sum(raisedTurncontinuationbet) as RaisedTurnContinuationBet,
sum(facingRivercontinuationbet) as FacingRiverContinuationBet,
sum(calledRivercontinuationbet) as CalledRiverContinuationBet,
sum(foldedtoRivercontinuationbet) as FoldedToRiverContinuationBet,
sum(raisedRivercontinuationbet) as RaisedRiverContinuationBet,

sum(vs_ep_raise_ip_fold) as vs_ep_raise_ip_fold,
sum(vs_ep_raise_ip_call) as vs_ep_raise_ip_call,
sum( vs_ep_raise_ip_raise) as vs_ep_raise_ip_raise,

sum(vs_mp_raise_ip_fold) as vs_mp_raise_ip_fold,
sum(vs_mp_raise_ip_call) as vs_mp_raise_ip_call,
sum(vs_mp_raise_ip_raise) as vs_mp_raise_ip_raise,

sum(vs_co_raise_ip_fold) as vs_co_raise_ip_fold,
sum(vs_co_raise_ip_call) as vs_co_raise_ip_call,
sum(vs_co_raise_ip_raise) as vs_co_raise_ip_raise,

sum(vs_sb_raise_ip_fold) as vs_sb_raise_ip_fold,
sum(vs_sb_raise_ip_call) as vs_sb_raise_ip_call,
sum(vs_sb_raise_ip_raise) as vs_sb_raise_ip_raise,

sum(vs_ep_raise_oop_fold) as vs_ep_raise_oop_fold,
sum(vs_ep_raise_oop_call) as vs_ep_raise_oop_call,
sum(vs_ep_raise_oop_raise) as vs_ep_raise_oop_raise,

sum(vs_mp_raise_oop_fold) as vs_mp_raise_oop_fold,
sum(vs_mp_raise_oop_call) as vs_mp_raise_oop_call,
sum(vs_mp_raise_oop_raise) as vs_mp_raise_oop_raise,

sum(vs_co_raise_oop_fold) as vs_co_raise_oop_fold,
sum(vs_co_raise_oop_call) as vs_co_raise_oop_call,
sum(vs_co_raise_oop_raise) as vs_co_raise_oop_raise,

sum(vs_bt_raise_oop_fold) as vs_bt_raise_oop_fold,
sum(vs_bt_raise_oop_call) as vs_bt_raise_oop_call,
sum(vs_bt_raise_oop_raise) as vs_bt_raise_oop_raise,

sum(ep_vs_raise_ip_fold) as ep_vs_raise_ip_fold,
sum(ep_vs_raise_ip_call) as ep_vs_raise_ip_call,
sum(ep_vs_raise_ip_raise) as ep_vs_raise_ip_raise,

sum(ep_vs_raise_oop_fold) as ep_vs_raise_oop_fold,
sum(ep_vs_raise_oop_call) as ep_vs_raise_oop_call,
sum(ep_vs_raise_oop_raise) as ep_vs_raise_oop_raise,

sum(mp_vs_raise_ip_fold) as mp_vs_raise_ip_fold,
sum(mp_vs_raise_ip_call) as mp_vs_raise_ip_call,
sum(mp_vs_raise_ip_raise) as mp_vs_raise_ip_raise,

sum(mp_vs_raise_oop_fold) as mp_vs_raise_oop_fold,
sum(mp_vs_raise_oop_call) as mp_vs_raise_oop_call,
sum(mp_vs_raise_oop_raise) as mp_vs_raise_oop_raise,

sum(co_vs_raise_ip_fold) as co_vs_raise_ip_fold,
sum(co_vs_raise_ip_call) as co_vs_raise_ip_call,
sum(co_vs_raise_ip_raise) as co_vs_raise_ip_raise,

sum(co_vs_raise_oop_fold) as co_vs_raise_oop_fold,
sum(co_vs_raise_oop_call) as co_vs_raise_oop_call,
sum(co_vs_raise_oop_raise) as co_vs_raise_oop_raise,

sum(bt_vs_raise_oop_fold) as bt_vs_raise_oop_fold,
sum(bt_vs_raise_oop_call) as bt_vs_raise_oop_call,
sum(bt_vs_raise_oop_raise) as bt_vs_raise_oop_raise,

sum(sb_vs_raise_ip_fold) as sb_vs_raise_ip_fold,
sum(sb_vs_raise_ip_call) as sb_vs_raise_ip_call,
sum(sb_vs_raise_ip_raise) as sb_vs_raise_ip_raise

from
CompiledResults_month CR,
CompiledPlayerResults_month CPR

where
CR.Player_ID in (123,16009,7076,16011,11785)
and CR.CompiledPlayerResults_ID = CPR.CompiledPlayerResults_ID
and CR.BBGroup_ID = 0
and CR.GameType_ID in (
Select GameType_ID
from GameTypes
where PokerGameType < 2 and PokerGame = 1
)
and CR.playedonmonth >= 200906
and CR.NumberOfPlayers Between 3 and 5

group by
CR.Player_ID

[consideratio]

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 in (123,16009,7076,16011,11785 )
and CR.CompiledPlayerResults_ID = PFA.CompiledPlayerResults_ID
and CR.BBGroup_ID = 0
and CR.GameType_ID in (
Select GameType_ID
from GameTypes
where PokerGameType < 2 and PokerGame = 1
)
and CR.playedonmonth >= 200906
and CR.NumberOfPlayers Between 3 and 5

group by
CR.Player_ID,
PFA.StreetActionType_ID

order by
player_ID,
StreetActionType_ID

[consideratio]

select
P.Player_ID,
N.note,
N.icon_id

from
players P

left join
hemplayernotes N

on
P.Player_ID = N.Player_ID

where
P.Player_id in (123,16009,7076,16011,11785)

order by
P.player_id

[consideratio]

So finally I guess my question would be:

How do I see/find/figure out the expressions used to calculate the stats that I can view in the HUD (configured in Player Preferences...)?

---

Also another question: (edit)
What is the difference between these two sums in Query #2?

vs_bt_raise_oop_fold
bt_vs_raise_oop_fold

[voodoopriester]

could someone direct me in the right direction please.
is it even possible to get the stats with the three compiled..._month tables positional 3bet and fold to 3bet (positional)?
Im working on this for days now and have no clue what im doeing wrong.

e.g. for foldTo3Bet as EP opener: 100.0*(sum(early1uopfr)+sum(early1limperpfr)+sum(e raly2andmorelimperspfr))/sum(foldtothreebet)

so if i understand anything right this means, that im the first raiser folding to a threebet.

or e.g. 3betting as CO: sum(cutoff2raiserspfr)/sum(cutoff1raiserhands)+sum(cutoff1raiserpluscalle rhands)
but i dont get the right %, no clue what im dowing wrong
hopefully someone can help me. thx a lot.

merry xmas

[TheZepper]

Hi voodoopriester, SQL Queries are not my strongest suit, but if you'll send me a PM maybe I can help you out.

[voodoopriester]

so i figurerd out most of the things. But I'm unable to get the fold to 3bet % from the BB
hopefully someone can help me out.