multiple query

[TROM]

I want choose from database PT3 in the sum of all players in the database with the PFR is less than 10%

I have query to determine what Cbet Flop all players have an average of a database

Code: Select all

SELECT COALESCE(CAST(SUM(CASE WHEN flg_f_cbet THEN 1 ELSE 0 END) AS real)
      / NULLIF(SUM(CASE WHEN flg_f_cbet_opp THEN 1 ELSE 0 END), 0), -1)
AS result
FROM holdem_hand_player_statistics as PS INNER JOIN holdem_hand_summary as HS ON (PS.id_hand = HS.id_hand)

in the PT3 this report also I cannt make and see the SQL syntax

[kraada]

I'm not certain what you are trying to accomplish here - could you please elaborate more? That query completes properly for me.

[TROM]

I want create query for all players in DB

for example we have 3 players in db

1-player Cbet 100%
2-player Cbet 50%
3-player Cbet 80%

after my query

Code: Select all

SELECT COALESCE(CAST(SUM(CASE WHEN flg_f_cbet THEN 1 ELSE 0 END) AS real)
      / NULLIF(SUM(CASE WHEN flg_f_cbet_opp THEN 1 ELSE 0 END), 0), -1)
AS result
FROM holdem_hand_player_statistics as PS INNER JOIN holdem_hand_summary as HS ON (PS.id_hand = HS.id_hand)

I have result 76,66% its average value of Cbet for 3 players

but I want query with pfr less than 10%

for example we have
1-player Cbet 100% pfr 50%
2-player Cbet 50% pfr 9%
3-player Cbet 80% pfr 8%

and after query I want get average value only for 2 and 3 player
Cbet 65% in result

How I can do it in postgresql ?

[kraada]

You can do this in PT3 in the Reports tab - add the stat you want to see (in this case PFR) and click the blue Filters link and filter for #PFR# < 10 and you'll see your stats then just for players that match that statistical pattern.

To do something like that in PostgreSQL you need to use a HAVING clause - you want your results from rows from players who have PFR < 10.

[TROM]

You can do this in PT3 in the Reports tab - add the stat you want to see (in this case PFR) and click the blue Filters link and filter for #PFR# < 10 and you'll see your stats then just for players that match that statistical pattern.

In PT3 I cant create report without player name

in report stats I have
Player
PFR
CBet Flop

in Filter i have
#PFR#<=10

and in logs this query very big and I think not exacly what I need
And if I delete Player from report stats I have red error in report

To do something like that in PostgreSQL you need to use a HAVING clause - you want your results from rows from players who have PFR < 10.

Can you show it on example with Cbet flop and PFR <= 10%
I read about HAVING clause but cant create query without errors in pgAdmin

[kraada]

The summary line at the bottom (when you have the Player stat displaying) gives you the total results for the entire sample - so for cbet flop you'll get the flop cbet average and for PFR you'll get the average PFR for the sample.

For the query:

SELECT COALESCE(CAST(SUM(CASE WHEN flg_f_cbet THEN 1 ELSE 0 END) AS real)
/ NULLIF(SUM(CASE WHEN flg_f_cbet_opp THEN 1 ELSE 0 END), 0), -1)
AS result
FROM holdem_hand_player_statistics as PS INNER JOIN holdem_hand_summary as HS ON (PS.id_hand = HS.id_hand) HAVING COALESCE(CAST(SUM(CASE WHEN cnt_p_raise > 0 THEN 1 ELSE 0 END) AS real)
/ NULLIF(SUM(CASE WHEN PS id_hand > 0 THEN 1 ELSE 0 END), 0), -1) < 10;

[TROM]

Code: Select all

SELECT COALESCE(CAST(SUM(CASE WHEN flg_f_cbet THEN 1 ELSE 0 END) AS real)
/ NULLIF(SUM(CASE WHEN flg_f_cbet_opp THEN 1 ELSE 0 END), 0), -1)
AS result
FROM holdem_hand_player_statistics as PS INNER JOIN holdem_hand_summary as HS ON (PS.id_hand = HS.id_hand) HAVING COALESCE(CAST(SUM(CASE WHEN cnt_p_raise > 0 THEN 1 ELSE 0 END) AS real)
/ NULLIF(SUM(CASE WHEN PS.id_hand > 0 THEN 1 ELSE 0 END), 0), -1) < 10

this query work in pgAdmin but all time return one value no matter what i write in the end <10 <20 or <100
and this value always like in this query whiout PFR condition

Code: Select all

SELECT COALESCE(CAST(SUM(CASE WHEN flg_f_cbet THEN 1 ELSE 0 END) AS real)
      / NULLIF(SUM(CASE WHEN flg_f_cbet_opp THEN 1 ELSE 0 END), 0), -1)
AS result
FROM holdem_hand_player_statistics as PS INNER JOIN holdem_hand_summary as HS ON (PS.id_hand = HS.id_hand)

[kraada]

I think it might be due to mismatched parentheses and coalesce but I'm not certain off the top of my head. That's the general way that having works though.

[TROM]

kraada
I try many variants but nothing change
I think all this part of query not work

Code: Select all

HAVING COALESCE(CAST(SUM(CASE WHEN cnt_p_raise > 0 THEN 1 ELSE 0 END) AS real)
/ NULLIF(SUM(CASE WHEN PS.id_hand > 0 THEN 1 ELSE 0 END), 0), -1) < 10

what I have to do to make it work ?

[kraada]

I'm sorry, my expertise in creating SQL queries only goes so far - try asking on the PostgreSQL forums.