Frequencies of Total in a Group By Stat

Discuss how to create custom stats, reports and HUD profiles and share your creations.

Moderators: WhiteRider, kraada, Flag_Hippo, morny

Frequencies of Total in a Group By Stat

Postby ethanrox » Sat Oct 10, 2020 5:23 pm

Hello

recently found this thread https://www.pokertracker.com/forums/viewtopic.php?f=61&t=44346 for a custom report based on hand group on the flop.
Is it possible to have it display % of the total as opposed to hand counts?
ethanrox
 
Posts: 52
Joined: Mon Sep 03, 2012 7:14 pm

Re: Frequencies of Total in a Group By Stat

Postby Flag_Hippo » Sun Oct 11, 2020 11:42 am

You will need to build custom statistics to do that. Also if the report is grouped like that then you will only see the % as a total of all hands on the summary row (for each grouped row a custom statistic like that would display 100% or 0%).
Flag_Hippo
Moderator
 
Posts: 11243
Joined: Tue Jan 31, 2012 7:50 am

Re: Frequencies of Total in a Group By Stat

Postby ethanrox » Sun Oct 11, 2020 2:27 pm

Flag_Hippo wrote:You will need to build custom statistics to do that. Also if the report is grouped like that then you will only see the % as a total of all hands on the summary row (for each grouped row a custom statistic like that would display 100% or 0%).


How do I see the %. If you take the "Bet Flop Size" statistic as example and the column behind it, you basically use it for the groupings. For the % of total stat it should use somehow the cnt_hands?
ethanrox
 
Posts: 52
Joined: Mon Sep 03, 2012 7:14 pm

Re: Frequencies of Total in a Group By Stat

Postby Flag_Hippo » Mon Oct 12, 2020 6:45 am

ethanrox wrote:How do I see the %.

Create a custom statistic or just calculate manually from the existing data in the report.
ethanrox wrote:If you take the "Bet Flop Size" statistic as example and the column behind it, you basically use it for the groupings. For the % of total stat it should use somehow the cnt_hands?

If it's made hands on the flop as per your first post then you would probably want to use cnt_f_saw and for flop bet sizes you would need to use cnt_f_bet. For example a custom statistic that tells you what proportion of your flop hands are high cards would look like:

cnt_f_highcard
Code: Select all
sum(if[cash_hand_player_combinations.flg_f_highcard, 1, 0])

Code: Select all
(cnt_f_highcard / cnt_f_saw) * 100
Flag_Hippo
Moderator
 
Posts: 11243
Joined: Tue Jan 31, 2012 7:50 am

Re: Frequencies of Total in a Group By Stat

Postby ethanrox » Mon Oct 12, 2020 9:20 am

Thanks for the solution.
I sure can create those stats but I will lose the grouping of hand-strengths which is useful for looking at other stats such as fold to cbet/call cbet etc.

Just for testing I created a Group By Column val_f_saw with definition
Code: Select all
if[cash_hand_player_statistics.flg_f_saw,1,0]
.
If I use this column in a IfSawFlop stat and want to see how many times I saw flop and how many I didn't, I created a custom report with this stat and Hands (which uses cnt_hands column).
Result is this https://imgur.com/a/nTWBBhw
Now I want to have a third stat in this report to tell me the percentage of the total for each group.
With some stackoverflow help + window functions I managed to get this in navicat for postgresql
https://imgur.com/a/3i8bpwh
with the following sql - just added the window function in the select clause of the query that was generated by PT4(logs etc).
Code: Select all
SELECT
   ( ( CASE WHEN ( cash_hand_player_statistics.flg_f_saw ) THEN 1 ELSE 0 END ) ) AS "val_f_saw",
   ( SUM ( ( CASE WHEN ( cash_hand_player_statistics.id_hand > 0 ) THEN 1 ELSE 0 END ) ) ) AS "cnt_hands" ,
   ( SUM ( ( CASE WHEN ( cash_hand_player_statistics.id_hand > 0 ) THEN 1 ELSE 0 END ) ) ) * 100.0 / SUM(( SUM ( ( CASE WHEN ( cash_hand_player_statistics.id_hand > 0 ) THEN 1 ELSE 0 END ) ) )) OVER () AS Percentage
FROM
   cash_hand_player_statistics
WHERE
   ( cash_hand_player_statistics.id_player = ( SELECT id_player FROM player WHERE player_name_search = E'ethanrox' AND id_site = '1100' ) )
   AND ( ( cash_hand_player_statistics.id_gametype = 1 ) )
GROUP BY
   ( ( CASE WHEN ( cash_hand_player_statistics.flg_f_saw ) THEN 1 ELSE 0 END ) )

The problem now is that it seems that I cannot use the window function in a column definition:
Code: Select all
sum(if[cash_hand_player_statistics.id_hand > 0, 1, 0])*100/sum(sum(if[cash_hand_player_statistics.id_hand > 0, 1, 0]) over ())

because of the error that I cannot use multiple aggregate functions in a single column.

Any ideas if that would be possible to overcome?
ethanrox
 
Posts: 52
Joined: Mon Sep 03, 2012 7:14 pm

Re: Frequencies of Total in a Group By Stat

Postby ethanrox » Mon Oct 12, 2020 10:28 am

Didn't see how to EDIT last post so:

Turns out if you use cnt_hands column and not the raw sql - you avoid the multiple aggregate functions error and things works well with the window function from last post.
ethanrox
 
Posts: 52
Joined: Mon Sep 03, 2012 7:14 pm


Return to Custom Stats, Reports and HUD Profiles

Who is online

Users browsing this forum: Google [Bot] and 1 guest

cron