filter all players that check/minraised the flop

[banshee]

Does anybody have an idea how to do that? I get it for just check/raising the flop but not for check/minraising. My idea was to use SQL and I found a stat that says "pecentage of pot raised on flop" what can maybe be used. The problem is that it's the first time I use SQL so could someone explain a little how to do that?

I would create a new column with this stat and then filter all players that c/r the flop and raised less or equal a certain amount of the pot. Is this possible? But how can I be sure that only minraises get filtered (lets say a minraise should be a raise of 2x-3x the original raise)

[Seitz333]

See if this gives you what you want.

This one shows the Players:

Code: Select all

SELECT player.player_name
FROM ((holdem_hand_player_statistics INNER JOIN holdem_hand_player_detail ON holdem_hand_player_statistics.id_hand = holdem_hand_player_detail.id_hand) INNER JOIN player ON holdem_hand_player_detail.id_player = player.id_player) INNER JOIN holdem_hand_summary ON holdem_hand_player_statistics.id_hand = holdem_hand_summary.id_hand
WHERE amt_f_bet_made = 0 AND flg_f_check_raise AND amt_f_raise_made = (amt_f_bet_facing*2) AND amt_f_bet_facing > 0
GROUP BY player.player_name
ORDER BY player.player_name ASC

This one shows Players and The hands they were involved with:

Code: Select all

SELECT player.player_name, holdem_hand_summary.hand_no, amt_f_raise_made, amt_f_bet_facing
FROM ((holdem_hand_player_statistics INNER JOIN holdem_hand_player_detail ON holdem_hand_player_statistics.id_hand = holdem_hand_player_detail.id_hand) INNER JOIN player ON holdem_hand_player_detail.id_player = player.id_player) INNER JOIN holdem_hand_summary ON holdem_hand_player_statistics.id_hand = holdem_hand_summary.id_hand
WHERE amt_f_bet_made = 0 AND flg_f_check_raise AND amt_f_raise_made = (amt_f_bet_facing*2) AND amt_f_bet_facing > 0
GROUP BY player.player_name, holdem_hand_summary.hand_no, amt_f_raise_made, amt_f_bet_made, amt_f_bet_facing
ORDER BY player.player_name ASC

Chuck

[banshee]

wow thanks, that looks complicating...hopefully I'll get that one day

It doesn't work the way I do it, somewhere must be a mistake. I create a new column under the Holdem Cash Player Statistics and fill in the expression. Then I save it under the name lets say "checkminraises" and under statistics I give it a new name and as value expression I enter the same name as I saved it under columns. Then I do a new report with this stat and run it but I get an error message.

[sookmctourie]

Chuck, how do I use this code as it is written here? Copy/Paste, Import?

Thanks
Art

[Seitz333]

1. Open pgAdmin, left click on the database you want to use.
2. Click on 'Tools' in the menu bar then select on 'Query tool'.
3. Copy and past the SQL statement into the top left part of the query tool.
4. Click on 'Query' in the menu bar, then select 'Execute'.

Chuck

[sookmctourie]

Thank you Chuck.
I had to download pgAdmin ..
the query was successful

[banshee]

Thanks alot, it works now.

I tried this query:

Code: Select all

SELECT player.player_name, holdem_hand_player_statistics.id_holecard, holdem_hand_summary.card_1, holdem_hand_summary.card_2, holdem_hand_summary.card_3, amt_f_raise_made, amt_f_bet_facing
FROM ((holdem_hand_player_statistics INNER JOIN holdem_hand_player_detail ON holdem_hand_player_statistics.id_hand = holdem_hand_player_detail.id_hand) INNER JOIN player ON holdem_hand_player_detail.id_player = player.id_player) INNER JOIN holdem_hand_summary ON holdem_hand_player_statistics.id_hand = holdem_hand_summary.id_hand
WHERE amt_f_bet_made = 0 AND flg_f_check_raise AND amt_f_raise_made = (amt_f_bet_facing*2) AND amt_f_bet_facing > 0
GROUP BY player.player_name, holdem_hand_player_statistics.id_holecard, holdem_hand_summary.card_1, holdem_hand_summary.card_2, holdem_hand_summary.card_3, amt_f_raise_made, amt_f_bet_facing
ORDER BY player.player_name ASC

But now I get cryptic numbers for the holecards. How can I get the real holecards? The documentation tells something about lookup_holecards but I'm not that good in SQL :/

[Seitz333]

Ok, it took some custom work but here is the SQL statement that will give you the holecards. This statement is for when the holecards are known. I had to add a lookup table that shows how the numerical number = actual card ( 1=2c, 2=3c, 3=4c, etc..) so you will need to add a lookup table to do this. I called my table 'lookup_cards', with two colums 'id_card', and 'card'. The 'id_card' column is the primary key 1-52 and the 'card' column is the actual card (jc, 4d, 3h, etc...)

If you are having problems creating the 'lookup_cards' table, send me a PM and I can email you a .backup file with just that table then you should be able to restore the table so the query will work.

Chuck

Code: Select all

SELECT player.player_name, amt_f_raise_made, amt_f_bet_facing, H1.card AS holecard1, H2.card as holecard2, F1.card AS flopcard1, F2.card AS flopcard2, F3.card AS flopcard3
FROM ((holdem_hand_player_statistics INNER JOIN holdem_hand_player_detail ON holdem_hand_player_statistics.id_hand = holdem_hand_player_detail.id_hand) INNER JOIN player ON holdem_hand_player_detail.id_player = player.id_player) INNER JOIN holdem_hand_summary ON holdem_hand_player_statistics.id_hand = holdem_hand_summary.id_hand, lookup_cards H1, lookup_cards H2, lookup_cards F1, lookup_cards F2, lookup_cards F3
WHERE amt_f_bet_made = 0 AND flg_f_check_raise AND amt_f_raise_made = (amt_f_bet_facing*2) AND amt_f_bet_facing > 0 AND holecard_1 > 0 AND holecard_2 > 0 and holecard_1 = H1.id_card AND holecard_2 = H2.id_card AND card_1 = F1.id_card AND card_2 = F2.id_card AND card_3 = F3.id_card
GROUP BY player.player_name, amt_f_raise_made, amt_f_bet_facing, holecard_1, holecard_2, H1.card, H2.card, F1.card, F2.card, F3.card
ORDER BY player.player_name ASC

lookup_cards table

1 = 2c
2 = 3c
3 = 4c
4 = 5c
5 = 6c
6 = 7c
7 = 8c
8 = 9c
9 = Tc
10 = Jc
11 = Qc
12 = Kc
13 = Ac
14 = 2d
15 = 3d
16 = 4d
17 = 5d
18 = 6d
19 = 7d
20 = 8d
21 = 9d
22 = Td
23 = Jd
24 = Qd
25 = Kd
26 = Ad
27 = 2h
28 = 3h
29 = 4h
30 = 5h
31 = 6h
32 = 7h
33 = 8h
34 = 9h
35 = Th
36 = Jh
37 = Qh
38 = Kh
39 = Ah
40 = 2s
41 = 3s
42 = 4s
43 = 5s
44 = 6s
45 = 7s
46 = 8s
47 = 9s
48 = Ts
49 = Js
50 = Qs
51 = Ks
52 = As

[banshee]

Big thanks for your work

But how to add that table? Sorry I'm a noob in SQL or dbadmin but I resolved to learn it. Are you sure that is the easiest way? The documentation somehow suggests to me that somehow it has to be possible to show your holecard without that much work. But as I said before I don't really have a clue

[Seitz333]

There might be an easier way to show the hole cards using custom reports in PT3, but I have not used the custom reports function so I'm not sure.

To add a table to the PT3 database using pgAdmin:

1. Left click on the database you want to add the table.
2. Expand the database (left click the plus sign).
3. Expand schema's.
4. Expand public.
5. Right click on tables and select 'New Table'.
6. In the name field type 'lookup_cards'.
7. In the Tablespace field click the dropdown and choose 'pg_default'. Then click 'ok'.
8. Right click on the new table and select 'New Object --> New Column'.
9. In the Name field type 'id_card'.
10. In the Data Type field select the dropdown and choose 'integer'.
11. Put a check in the 'NULL' checkbox. Select 'ok'.
12. Right click on the new table and select "New Object --> New Primary Key'.
13. In the Name field type 'lookup_cards_primary_key'.
14. In the Tablespace field select the dropdown and choose 'pg_default'.
15. Click on the 'Columns' tab then click on the dropdown at the bottom and choose 'id_card'. Then click 'Add'. Now click 'ok'.
16. Right click on the new table and select 'New Object --> New Column'.
17. In the Name field type 'card'.
18. In the Data Type field select the dropdown and choose 'character varying'.
19. In the Length field enter '2'.
20. Put a check in the 'NULL' checkbox. Select 'ok'.

DONE!!!!

Don't forget you need to add the 1-52 primary keys and the corresponding cards