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

0
votes
select *
FROM player_game_stats pgs INNER JOIN games g ON pgs.game_id = g.id where pgs.player_id=265
and (g.part_of_season='REGULAR' or g.part_of_season='BUBBLE') order by start_date desc limit 10;

gives me 10 rows of the data i want

however

select avg(points)
FROM player_game_stats pgs INNER JOIN games g ON pgs.game_id = g.id where pgs.player_id=265
and (g.part_of_season='REGULAR' or g.part_of_season='BUBBLE') order by start_date desc limit 10;

gives me a sql error of ERROR: column "g.start_date" must appear in the GROUP BY clause or be used in an aggregate function

why is this a problem and how can i solve it?

1
sqlpostgresql
Which tables contains the points column? – a_horse_with_no_name
Why do you want to order if you're only getting a single value? – HoneyBadger
@a_horse_with_no_name table player_game_stats contains points and table games contains start_date – Francis C
As mentioned already, your AVG() query without a GROUP BY will return 1 row only. No need to use ORDER BY. – jarlh
@HoneyBadger i want it in the order of start date, take the top 10, and then avg those numbers – Francis C

1 Answers

1
votes

I want it in the order of start date, take the top 10, and then avg those numbers

You need a derived table to do that:

select avg(points)
from (
  select pgs.points
  FROM player_game_stats pgs 
    JOIN games g ON pgs.game_id = g.id 
  where pgs.player_id=265
    and g.part_of_season IN ('REGULAR', 'BUBBLE') 
  order by start_date desc 
  limit 10
) t;