How to add group_by to fix a postgresql group_by error

Question

I am following a railscast episode but I'm using postgresql and am getting a group_by error:

PG::Error: ERROR:  column "ratings.created_at" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT created_at, sum(score) as total_score FROM "ratings" ...
               ^
: SELECT created_at, sum(score) as total_score FROM "ratings"  WHERE ("ratings"."created_at" BETWEEN '2014-01-02 00:00:00.000000' AND '2014-01-23 13:43:06.187741') GROUP BY date(created_at)

How should I modify my code below to include group_by created_at

def self.chart_data(start = 3.weeks.ago)
 total_votes = votes_by_day(start)
 (start.to_date..Date.today).map do |date|
 {
  created_at: date,
  total_score: total_prices[date] || 0
 }
 end
end

def self.votes_by_day(start)
 ratings = where(created_at: start.beginning_of_day..Time.zone.now)
 ratings = ratings.group("date(created_at)")
 ratings = ratings.select("created_at, sum(score) as total_score")
 ratings.each_with_object({}) do |rating, scores|
  scores[rating.created_at.to_date] = rating.total_score
 end
end

Any chance you're running an old PostgreSQL version? What does select version() output? (Haven't really looked at question to see whether the issue I'm thinking of applies, but you should include your Pg version anyway). — Craig Ringer
– Craig Ringer, Commented Jan 23, 2014 at 14:17

johnsorrentino · Accepted Answer · 2014-01-23 23:17:22Z

2

Your group by clause and your select clause have different attributes. If you're grouping by "date(created_at)" then you can no longer select "created_at."

def self.votes_by_day(start)
  ratings = where(created_at: start.beginning_of_day..Time.zone.now)
  ratings = ratings.group("date(created_at)")
  ratings = ratings.select("date(created_at), sum(score) as total_score")
  ratings.each_with_object({}) do |rating, scores|
  scores[rating.created_at.to_date] = rating.total_score
end

answered Jan 23, 2014 at 23:17

johnsorrentino

2,7311 gold badge18 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

grabury Over a year ago

I grouped_by created_at instead of date(created_at) so that I could use created_at in: scores[rating.created_at.to_date]and it worked. Thank you.

Collectives™ on Stack Overflow

How to add group_by to fix a postgresql group_by error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related