Frequencies of Total in a Group By Stat

[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?

[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%).

[ethanrox]

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?

[Flag_Hippo]

How do I see the %.

Create a custom statistic or just calculate manually from the existing data in the report.

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

[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?

[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.