Easy Poker DB Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Easy Poker DB Query

Author

Topic

gaming_mouse
Starting Member

4 Posts

Posted - 2006-05-26 : 15:05:48

It should be easy, anyway. My SQL is rusty, though, and I can't figure it out.

My database contains poker data with the following relevant colums (I've included sample data):


game_id player_id number_of_players pf_raise saw_flop
21      2         4                 0        1    
21      8         4                 0        1
21      372       4                 0        0
21      76        4                 1        1

game_id and player_id are globally unique.

number_of_players tells us how many players were dealt cards for that game_id.

pf_raise is boolean -- tells us if the player_id in question raised before the flop. Thus in the sample data we could have had all 0's or all 1's, or 2 1's, or 3 1's.

saw_flop -- boolean again. tells us if the player saw the flop or not. Again, all combinations of 0's and 1's are possible in theory.

MY QUESTION: I need SQL to query for all game_id's in which exactly 3 players saw the flop AND in which there was no pf_raise by ANY player. The DB is Access, but if you are familiar with a different setup that's fine: I'm not interested in the niceties of the syntax so much as the conceptual solution. But I would like to see SQL code, if possible.

Thanks in advance for any help,
gm

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-26 : 15:32:30

[code]
select
game_id
from
Mytable
Group by
game_id
having
sum(convert(int,pf_raise)) = 0 and
sum(convert(int,saw_flop)) = 3

[/code]

CODO ERGO SUM

gaming_mouse
Starting Member

4 Posts

Posted - 2006-05-26 : 16:03:19

Michael,

Thank you very much. I hope you can help with another quick one....

Two other columns in that table are "total_won" and "hole_cards". The following query will select display the hole cards and the amount won for all steal attempts made on the button:


SELECT GP.total_won, GP.hole_cards
FROM game_players AS GP
AND GP.button = 1
AND GP.hole_cards IS NOT NULL
AND GP.steal_attempted = 1

But in the results here each hand (eg, TJo or AA) will appear many times. What I am really interested in is the average amount won with each of these hands. So ideally the results will show each hand only once, and next to it the average won with that hand. I believe there is a way to do this, but I can't remember it. I'm guessing that you can?? :)

Thanks again
gm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources