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/nTWBBhwNow 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/3i8bpwhwith 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?