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.

Solution 1

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.

Solution 2

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")

Comments

[Reuben]

Gotcha, so instead of using the wildcard * I should instead explicitly list out every column name in the select clause like this? @albums = Album.joins(:reviews).select("reviews.rating, albums.id, albums.name, albums.artist, albums.picture, avg(reviews.rating) as average_rating").group("albums.id").order("average_rating DESC")

[slash]

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

[slash]

I'm not so sure which fields you actually want to select. But in your comment you're trying to select reviews.rating and avg(reviews.rating). So in one result row you are trying to get the current rows rating and the average rating at once? You could edit your question and add the table structures of both, reviews and albums, as well as what fields you are expecting to receive.

[Reuben]

Your previous comment did the trick. For @albums I am trying to get all the fields in my Album model ordered by how highly rated the albums are to display in my view. My last comment was my poor attempt at understanding what your first suggestion (remove the wildcard from select) meant.