GroupingError: ERROR: column must appear in the GROUP BY clause or be used in an aggregate function

Question

I have code in my controller that is ranking albums by the highest average review rating (used code from this solution How to display highest rated albums through a has_many reviews relationship):

@albums = Album.joins(:reviews).select("*, avg(reviews.rating) as average_rating").group("albums.id").order("average_rating DESC") 

This code works perfectly in my development environment (sqlite3), however when I pushed the code to heroku and to postgresql I got this error:

PG::GroupingError: ERROR:  column "reviews.id" must appear in the GROUP BY clause or be used in an aggregate function 

I realize this is a fairly common problem, I am a bit inexperienced with SQL so I am having trouble refactoring the code so it will work in both my development and production environments.

Answer 1

Just would like to share this code on ruby using active record (sinatra)

I had to add "group by" to an "order by" function, so line of code ...

from:

@models = Port.all.order('number asc') 

to:

@models = Port.select(:id, :device_id, :number, :value, :sensor, :UOM).all.order('number asc').group(:id,:sensor,:UOM) 

and it worked perfect, just remember the ID field in this case "Port.id" must be added to the group clause otherwise will raise this error, and as @slash mentioned you can not achieve this with special functions (select implicitly through the wildcard * or in my case using "all")

Answer 2

You are not allowed to select reviews.id (selected implicitly through the wildcard *) without adding it to the GROUP BY clause or applying an aggregate function like avg(). The solution is to do one of the following:

1. Remove the wildcard * from your select
2. Add the field reviews.id to your group clause
3. Select reviews.id explicitly and apply an aggregate function to it (e.g. sum(reviews.id))
4. Replace the wildcard * with the table-specific wildcard albums.*

The second and third option do not make much sense in your scenario though. Based on your comment, I added option four.