Complex (?) Query Question

[gamingmouse]

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

[tt]
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
[/tt]

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

 

[Golom]

Select Game_ID

From myTable

Group By Game_ID

Having Abs(SUM(saw_flop)) = 3 AND Abs(SUM(pf_raise)) = 0

I stuck in the "Abs" function because Access uses "-1" for TRUE (not +1)

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[gamingmouse]

Golom,

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:

[tt]
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
[/tt]


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

 

[Golom]

SELECT GP.hole_cards, AVG(GP.total_won) As [Average Won]

FROM game_players AS GP

WHERE GP.button = 1
  AND GP.hole_cards IS NOT NULL
  AND GP.steal_attempted = 1

Group by GP.hole_cards

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[gamingmouse]

Golom,

Thanks again. I have now adapted your code to the actual table, which has a couple idiosyncrasies, and I am getting a strange result.

[tt]
SELECT Avg(BS.total_won - BS.total_bet) AS [Avg Won], BS.hole_cards AS [Hole Cards], count(*) AS [Sample Size]
FROM [SELECT *
FROM game_players AS GP
WHERE GP.hole_cards IS NOT NULL
AND GP.steal_attempted = 1
AND GP.button = GP.seat_number
AND ( GP.player_id = 1794 OR GP.player_id = 6)
]. AS BS
GROUP BY BS.hole_cards;
[/tt]

It turns out that the button is not a 0/1 field. The button is a number, which contains the seat number of the player who was the button that hand. So I am the button when my seat number = button. 1794 and 6 are the player id's for my 2 screen names.

The above query, however, only returns 32 hands; this cannot be correct because there are 169 possible starting hands, and my stealing percentage on the button is much higher than 32/169.

But I cannot see the error in the above query. Perhaps you can?

Thanks again,
gm

 

[Golom]

There doesn't seem to be much reason for the sub-query

SELECT Avg(total_won - total_bet) AS [Avg Won]
       , hole_cards AS [Hole Cards]
       , count(*) AS [Sample Size]

FROM game_players 

WHERE hole_cards      IS NOT NULL
  AND steal_attempted = 1
  AND button          = seat_number
  AND player_id       IN (6,1794)

GROUP BY hole_cards;

As to the rest ...
I don't know how you determined that there are 169 possible starting hands but the real question is "Are there 169 possible starting hands when you were the button?"

Possibly something like

SELECT "With The Button" As [Button State]
       , Abs(SUM(steal_attempted)) AS [Steals Attempted]
       , Count(*) - Abs(SUM(steal_attempted)) AS [No Steals]
       , count(*) AS [Sample Size]

FROM game_players 

WHERE hole_cards      IS NOT NULL
  AND steal_attempted = 1
  AND button          = seat_number
  AND player_id       IN (6,1794)

GROUP BY hole_cards

UNION ALL

SELECT "Without The Button" As [Button State]
       , Abs(SUM(steal_attempted)) AS [Steals Attempted]
       , Count(*) - Abs(SUM(steal_attempted)) AS [No Steals]
       , count(*) AS [Sample Size]

FROM game_players 

WHERE hole_cards      IS NOT NULL
  AND steal_attempted = 1
  AND button          <> seat_number
  AND player_id       IN (6,1794)

GROUP BY hole_cards

can give you some clues

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]