GroupingError：错误：列必须出现在GROUP BY子句或聚合函数中使用 [英] GroupingError: ERROR: column must appear in the GROUP BY clause or be used in an aggregate function

问题描述

我有code在我的控制器是由最高的平均评价得分排名专辑（使用code，从这个解决方案<一href="http://stackoverflow.com/questions/20852887/how-to-display-highest-rated-albums-through-a-has-many-reviews-relationship">How通过的has_many审查的关系显示收视率最高的专辑）：

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

这code完全在我的开发环境（sqlite3的），但是当我推开code到Heroku和到PostgreSQL我得到这个错误：

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

我意识到这是一个相当普遍的问题，我有点缺乏经验与SQL所以我无法重构code所以它会在我的两个开发和生产环境。

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.

推荐答案

您不允许通过通配符 reviews.id （选择隐> * ），而不将其添加到 GROUP BY 条款或应用聚合函数如平均（）。解决的办法是请执行下列操作之一：

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. 删除通配符 * 从你的选择
2. 字段 reviews.id 添加到组第
3. 选择 reviews.id 明确并应用聚合函数，它（例如：和（reviews.id））
4. 替换通配符 * 与表特定通配符专辑。*

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.

这篇关于GroupingError：错误：列必须出现在GROUP BY子句或聚合函数中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！