Player A vs Player B stats

[pingvvino]

I'm looking for stats (queries) which allow me to measure some complex situation. I'd like to have statistic between two players.
For example 3bet player A vs player B, 4bet player A vs player B and ideally resteal as well. kraada has already helped me with common hands for player A and player B query (https://na3.pokertracker.com/forums/vie ... 18&t=42887). Is it possible to create similar query but for mentioned statistics?

[kraada]

You can add more restrictions in those subqueries to narrow down other cases, though to build full statistics you'll need to use CASE WHEN statements. The easiest way to see how to construct those is to create a custom report with a filter like #PFR# > 5 or some such and look at our log file - you can see how it does the CASE WHEN in the HAVING clause of the query generated to build that report. You can find our log file in the C:\Program Files (x86)\PokerTracker 3\ directory - it's called PokerTracker.log and is available anytime that PT3 is started with Logging Enabled (and you can enable logging also via the Configure -> Options menu which keeps it on even when started with the normal link).

[pingvvino]

I know how to intercept queries, but I can't find PRF filter.There are a lot of available filters with hands, actions, facing etc ...
Could you please write me an example with the some easiest stats (like 3bet)?

[pingvvino]

When I use this query:

SELECT (case when (count(*)!=0)
then ( cast(sum(cnt_p_raise) as real) / cast(sum(cnt_p_call) as real))
else (-1)
end) as result
from holdem_hand_player_statistics hhps, player p, holdem_hand_player_statistics hhps2, player p2
where hhps.id_player = p.id_player
and hhps.id_hand = hhps2.id_hand
and hhps2.id_player = p2.id_player
and p.player_name = 'PLAYER A'
and p2.player_name = 'PLAYER B'

I have following error:
ERROR: column reference "cnt_p_raise" is ambiguous

[WhiteRider]

I suspect that you will need to replace that column (and cnt_p_call) with the actual column expressions, which you can see via the Configure > Stats window.

[BillGatesIII]

When I use this query:

SELECT (case when (count(*)!=0)
then ( cast(sum(cnt_p_raise) as real) / cast(sum(cnt_p_call) as real))
else (-1)
end) as result
from holdem_hand_player_statistics hhps, player p, holdem_hand_player_statistics hhps2, player p2
where hhps.id_player = p.id_player
and hhps.id_hand = hhps2.id_hand
and hhps2.id_player = p2.id_player
and p.player_name = 'PLAYER A'
and p2.player_name = 'PLAYER B'

I have following error:
ERROR: column reference "cnt_p_raise" is ambiguous

I don't know the PT3 table definitions but I suspect cnt_p_raise and cnt_p_call are in holdem_hand_player_statistics so you have to specify from which table you want to read these columns (hhps or hhps2).

[kraada]

Just to confirm - you will want either hhps or hhps2 for those columns.

[pingvvino]

Something like that:

Code: Select all

select (case when (count(*)!=0) then
         cast(sum(case when hhps.flg_p_face_raise AND hhps.flg_p_fold = false then 1 else 0 end) as real) /
         cast(sum(case when hhps.flg_p_face_raise then 1 else 0 end) as real)
      else (-1) end) as result
from holdem_hand_player_statistics hhps, player p, holdem_hand_player_statistics hhps2, player p2
where hhps.id_player = p.id_player
  and hhps.id_hand = hhps2.id_hand
  and hhps2.id_player = p2.id_player
  and p.player_name = 'A'
  and p2.player_name = 'B'

?

[kraada]

That should fix the error and count up the stats for player A when player B is dealt in. Isn't that what you see when you run it?