Page 1 of 1

Frequencies of Total in a Group By Stat

PostPosted: Sat Oct 10, 2020 5:23 pm
by ethanrox
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?

Re: Frequencies of Total in a Group By Stat

PostPosted: Sun Oct 11, 2020 11:42 am
by Flag_Hippo
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%).

Re: Frequencies of Total in a Group By Stat

PostPosted: Sun Oct 11, 2020 2:27 pm
by ethanrox
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?

Re: Frequencies of Total in a Group By Stat

PostPosted: Mon Oct 12, 2020 6:45 am
by Flag_Hippo
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

Re: Frequencies of Total in a Group By Stat

PostPosted: Mon Oct 12, 2020 9:20 am
by ethanrox
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?

Re: Frequencies of Total in a Group By Stat

PostPosted: Mon Oct 12, 2020 10:28 am
by ethanrox
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.

highfalutin