Aggregate and LIMIT

[skyd1v3r]

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!

[advis0r]

Code: Select all

SELECT SUM(foo.vpip)
FROM (SELECT (CASE WHEN flg_vpip='t' THEN 1 ELSE 0 END) as vpip, id_player
FROM holdem_hand_player_statistics
WHERE id_player=1
ORDER BY date_played DESC
LIMIT 100) as foo
GROUP BY foo.id_player

[skyd1v3r]

Great!

Worked out of the box, thank you very much.

[skyd1v3r]

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

[advis0r]

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.

[skyd1v3r]

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!

[advis0r]

Code: Select all

WITH bla160 as (SELECT (CASE WHEN flg_vpip='t' THEN 1 ELSE 0 END) as vpip, id_player
FROM holdem_hand_player_statistics
WHERE id_player=1
ORDER BY date_played DESC
LIMIT 160),
bla80 as (SELECT * FROM bla160 LIMIT 80),
bla30 as (SELECT * FROM bla80 LIMIT 30),
foo160 as (SELECT sum(vpip) as bar FROM bla160 GROUP BY id_player),
foo80 as (SELECT sum(vpip) as bar FROM bla80 GROUP BY id_player),
foo30 as (SELECT sum(vpip) as bar FROM bla30 GROUP BY id_player)
SELECT
foo160.bar as vpip160,
ROUND((foo160.bar/160.0)*100, 2) as vpip160_perc,
foo80.bar as vpip80,
ROUND((foo80.bar/80.0)*100, 2) vpip80_perc,
foo30.bar as vpip30,
ROUND((foo30.bar/30.0)*100, 2) vpip30_perc
FROM foo160, foo80, foo30