Player A vs Player B stats

Forum for users that want to write their own custom queries against the PT database either via the Structured Query Language (SQL) or using the PT3 custom stats/reports interface.

Moderator: Moderators

Player A vs Player B stats

Postby pingvvino » Wed May 08, 2013 5:21 pm

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?
pingvvino
 
Posts: 18
Joined: Fri Jul 20, 2012 12:41 pm

Re: Player A vs Player B stats

Postby kraada » Wed May 08, 2013 5:47 pm

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).
kraada
Moderator
 
Posts: 54431
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: Player A vs Player B stats

Postby pingvvino » Sat May 11, 2013 8:24 am

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
 
Posts: 18
Joined: Fri Jul 20, 2012 12:41 pm

Re: Player A vs Player B stats

Postby pingvvino » Sat May 11, 2013 3:01 pm

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
pingvvino
 
Posts: 18
Joined: Fri Jul 20, 2012 12:41 pm

Re: Player A vs Player B stats

Postby WhiteRider » Sat May 11, 2013 4:00 pm

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.
WhiteRider
Moderator
 
Posts: 54017
Joined: Sat Jan 19, 2008 7:06 pm
Location: UK

Re: Player A vs Player B stats

Postby BillGatesIII » Sat May 11, 2013 5:20 pm

pingvvino wrote: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).
BillGatesIII
 
Posts: 740
Joined: Fri Dec 16, 2011 6:50 pm

Re: Player A vs Player B stats

Postby kraada » Mon May 13, 2013 7:59 am

Just to confirm - you will want either hhps or hhps2 for those columns.
kraada
Moderator
 
Posts: 54431
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: Player A vs Player B stats

Postby pingvvino » Mon May 13, 2013 5:43 pm

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'
?
pingvvino
 
Posts: 18
Joined: Fri Jul 20, 2012 12:41 pm

Re: Player A vs Player B stats

Postby kraada » Tue May 14, 2013 8:26 am

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?
kraada
Moderator
 
Posts: 54431
Joined: Wed Mar 05, 2008 2:32 am
Location: NY


Return to Custom Stats, Reports, and SQL [Read Only]

Who is online

Users browsing this forum: No registered users and 18 guests

cron
highfalutin