Weird small difference in AFq between PT and my SQL

[PeteX]

I'm trying to do a query for my own use where I list VPIP,PFR,AFq and WtSD from all players having >100 hands in the database.

Here is the SQL:

Code: Select all

select p.id_player as PlayerId,
ROUND((CAST(SUM(CASE WHEN hhps.flg_vpip THEN 1 ELSE 0 END) AS NUMERIC) / count(*))*100,2) as vpip,
ROUND((CAST(SUM(CASE WHEN hhps.cnt_p_raise>0 THEN 1 ELSE 0 END) AS NUMERIC) / count(*))*100,2) as pfr,
ROUND((CAST(SUM(CASE WHEN hhps.flg_showdown THEN 1 ELSE 0 END) AS NUMERIC) / SUM(CASE WHEN hhps.flg_f_saw THEN 1 ELSE 0 END))*100,2) as wtsd,
ROUND(
(CAST(SUM(CASE WHEN hhps.cnt_f_raise>0 THEN 1 ELSE 0 END) + SUM(CASE WHEN hhps.flg_f_bet THEN 1 ELSE 0 END) +
SUM(CASE WHEN hhps.cnt_t_raise>0 THEN 1 ELSE 0 END) + SUM(CASE WHEN hhps.flg_t_bet THEN 1 ELSE 0 END) +
SUM(CASE WHEN hhps.cnt_r_raise>0 THEN 1 ELSE 0 END) + SUM(CASE WHEN hhps.flg_r_bet THEN 1 ELSE 0 END)
AS NUMERIC)
/
CAST((SUM(CASE WHEN hhps.cnt_f_call>0 THEN 1 ELSE 0 END) + SUM(CASE WHEN hhps.flg_f_fold THEN 1 ELSE 0 END) +
SUM(CASE WHEN hhps.cnt_t_call>0 THEN 1 ELSE 0 END) + SUM(CASE WHEN hhps.flg_t_fold THEN 1 ELSE 0 END) +
SUM(CASE WHEN hhps.cnt_r_call>0 THEN 1 ELSE 0 END) + SUM(CASE WHEN hhps.flg_r_fold THEN 1 ELSE 0 END) +
SUM(CASE WHEN hhps.cnt_f_raise>0 THEN 1 ELSE 0 END) + SUM(CASE WHEN hhps.flg_f_bet THEN 1 ELSE 0 END) +
SUM(CASE WHEN hhps.cnt_t_raise>0 THEN 1 ELSE 0 END) + SUM(CASE WHEN hhps.flg_t_bet THEN 1 ELSE 0 END) +
SUM(CASE WHEN hhps.cnt_r_raise>0 THEN 1 ELSE 0 END) + SUM(CASE WHEN hhps.flg_r_bet THEN 1 ELSE 0 END)) as numeric)) * 100,2) as AFq,
p.player_name as Name,
count(*) as HandCount
from player p, holdem_hand_player_statistics hhps
where p.id_player = hhps.id_player
group by p.id_player,p.player_name
having count(hhps.id_hand)>100
order by count(hhps.id_hand) desc


When comparing my players values from the query to the numbers on PT3, I have VPIP, PFR and WtSD spot on with 9425 hands. But AFq is a bit off which is very strange. PT3 shows my AFq being 50.87 (on the General-tab, Player Statistics-table, no filters on, all 9425 hands showing over 4 limits). But my query gives me 50.71 as AFq.

What am I missing here? Do I have some typo somewhere or what might be the problem?

[WhiteRider]

I can't see anything obvious in your query - try narrowing it down a bit and see if you can find a subset of hands where the difference occurs. e.g. a specific date / limit combination. Then we can investigate it more thoroughly.

[PeteX]

I can't see anything obvious in your query - try narrowing it down a bit and see if you can find a subset of hands where the difference occurs. e.g. a specific date / limit combination. Then we can investigate it more thoroughly.

I will try that.

Could you take that query and execute it and compare the returned AFq figure with the figure given by PT3 and see if they match.

BTW: I did try the same query for Flop AFq, just taking the flop-values into account and same thing: There was a small difference between the AFq(flop) figure from my query and the one shown on the Detail Report-page. Still, the hand count was exactly the same on the query and on PT3 tables and reports so one would imagine that all hands were taken into account on both occasions.

[WhiteRider]

Yes, I'll do some investigation into this myself too tomorrow if I have some time.

[PeteX]

Ok, I checked the AFq of a few more players from PT3 vs my query results and all of them seem to have some differences:
There were results like this (first is PT3 AFq, then AFq returned from my query):
- 35.80 vs 35.81 (this could be due to rounding)
- 48.01 vs 47.90 (but these certainly shouldn't)
- 59.52 vs 58.97
- 57.03 vs 57.14
- 20.77 vs 20.50
etc

So, if the formula for calculating AFq really is ((flop-bets + turn-bets + river-bets + flop-raises + turn-raises + river-raises) / (flop-bets + turn-bets + river-bets + flop-raises + turn-raises + river-raises + flop-calls + flop-folds + turn-calls + turn-folds + river-calls + river-folds)) * 100 then I'm starting to wonder that does PT3 calculate the AFq value correctly? I mean, could there be some minor error where some source value for the AFq is taken from an incorrect variable? The values are off so little, that one could easily imagine that some variable on the AFq formula gets its value from some incorrect source value, which is close to the correct value in magnitude, but not quite.

Just to confirm: The preflop numbers should not be included (as I'm not), right?

Could someone please take the SQL from the op and run it with pgAdmin III and compare the AFq value of some player with the one returned by PT3 on the Player Statistics-grid on General-tab or on the Details-report.

[WhiteRider]

Your assumptions are all correct. I haven't had time to look at this yet, but I'll get to it soon.
Obviously it might take a while to identify the specific issue, though, so please be patient.

[WhiteRider]

Your query gives the exactly same value for me as PT3 does, but it isn't the same for all players.
I've found a player with 101 hands who shows a difference so I'll investigate his actions and see if I can find out where the difference is.
I'll report back..

[WhiteRider]

Sorry - I've just worked out the problem with your query.
You are only counting each raise and call once per street.
If you call twice on the flop for instance, AFq counts this twice.
So "CASE WHEN hhps.cnt_t_call>0 THEN 1 ELSE 0 END" is wrong - you should just sum "hhps.cnt_t_call".

[PeteX]

Sorry - I've just worked out the problem with your query.
You are only counting each raise and call once per street.
If you call twice on the flop for instance, AFq counts this twice.
So "CASE WHEN hhps.cnt_t_call>0 THEN 1 ELSE 0 END" is wrong - you should just sum "hhps.cnt_t_call".

:oops:

Right you are!!! That's a very good explanation why the values differed small amounts, obv because if nobody ever called/raised twice on a street, then the value was accurate and the more of those one did, the larger the difference was.

Well, that's obvious when I think of it now... ;)

Thanks a million!!!

[WhiteRider]

No problem. If you notice anything else, please let us know.