Edit Question - KnowledgeHub

Title Body

I'm trying to put together a query that will retrieve the statistics of a user (profit/loss) as a cumulative result, over a period of time. Here's the query I have so far: SELECT p.name, e.date, sum(sp.payout) OVER (ORDER BY e.date) - sum(s.buyin) OVER (ORDER BY e.date) AS "Profit/Loss" FROM result r JOIN game g ON r.game_id = g.game_id JOIN event e ON g.event_id = e.event_id JOIN structure s ON g.structure_id = s.structure_id JOIN structure_payout sp ON g.structure_id = sp.structure_id AND r.position = sp.position JOIN player p ON r.player_id = p.player_id WHERE p.player_id = 17 GROUP BY p.name, e.date, e.event_id, sp.payout, s.buyin ORDER BY p.name, e.date ASC The query will run. However, the result is slightly incorrect. The reason is that an event can have multiple games (with different sp.payouts ). Therefore, the above comes out with multiple rows if a user has 2 results in an event with different payouts (i.e. there are 4 games per event, and a user gets £20 from one, and £40 from another). The obvious solution would be to amend the GROUP BY to: GROUP BY p.name, e.date, e.event_id However, Postgres complains at this as it doesn't appear to be recognizing that sp.payout and s.buyin are inside an aggregate function. I get the error: column "sp.payout" must appear in the GROUP BY clause or be used in an aggregate function I'm running 9.1 on Ubuntu Linux server. Am I missing something, or could this be a genuine defect in Postgres?

Tags (comma-separated) Cancel