PokerTracker

Hello,

I want to know how often a player went VPIP the last 100 hands.

Code: Select all

SELECT (CASE WHEN flg_vpip='t' THEN 1 ELSE 0 END)
FROM holdem_hand_player_statistics 
WHERE id_player=2
ORDER BY date_played
LIMIT 100;

basicly works and delivers 10110100010...,
yet when I try to apply the aggregate function

Code: Select all

SELECT SUM(CASE WHEN flg_vpip='t' THEN 1 ELSE 0 END)
FROM holdem_hand_player_statistics 
WHERE id_player=2
ORDER BY date_played
LIMIT 100;

I get

Code: Select all

ERROR: column "holdem_hand_player_statistics.date_played" must appear in the GROUP BY clause or be used in an aggregate function

While I understand the error, I do not see how I could fix it.

Thanks!

Great!

Worked out of the box, thank you very much.

Unfortunately my SQL-knowledge is way below what I´ve belived.

I am trying to get the sum of VPIP´s of the last 30, 80 and 160 hands in one query. (thats´s why.) Herefore I edited the code the following way:

Code: Select all

SELECT SUM(foo30.vpip30),SUM(foo80.vpip80)
FROM

(SELECT (CASE WHEN flg_vpip='t' THEN 1 ELSE 0 END) as vpip30, id_player
FROM holdem_hand_player_statistics
WHERE id_player=(SELECT id_player FROM player WHERE player_name='JohnDoe')
ORDER BY date_played DESC
LIMIT 30
) as foo30,

(SELECT (CASE WHEN flg_vpip='t' THEN 1 ELSE 0 END) as vpip80, id_player
FROM holdem_hand_player_statistics
WHERE id_player=(SELECT id_player FROM player WHERE player_name='JohnDoe')
ORDER BY date_played DESC
LIMIT 80
) as foo80

GROUP BY foo30.id_player;


While I expected something like 10/40 or alike, I got

Code: Select all

 sum | sum
----+----
 720 | 600
(1 row)

Obviously a player cannot go 720 times VPIP in 30 hands.

Thankfull for any suggestions,

cheers

try with common table expression:

http://www.postgresql.org/docs/8.4/inte ... -with.html

i.e. 1 query with last 160 hands, from this aggregate last 80 hands and so on. on the outer query get sum of each of the inner queries.

Ok that´s somewhat above my head.

It´s unpleasing to ask you for another favor, as you´ve helped me a lot already.
Yet when you have a minute in spare, could you elaborate how that works?

Thanks a lot in advance!