GroupingError: ERROR: column must appear in the GROUP BY clause or be used in an aggregate function
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:
- Remove the wildcard
*
from your select - Add the field
reviews.id
to your group clause - Select
reviews.id
explicitly and apply an aggregate function to it (e.g.sum(reviews.id)
) - Replace the wildcard
*
with the table-specific wildcardalbums.*
The second and third option do not make much sense in your scenario though.Based on your comment, I added option four.
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")
You cannot combine SELECT *
with GROUP BY some_column
in Postgres (unless some_column
is the PK), because that's a contradiction. All non-aggregated columns (used in the SELECT
, HAVING
or ORDER BY
clause outside an aggregate function) must be in the GROUP BY
list - where the primary key column can replace all columns of a table. Else it is undefined which value to pick from the aggregated set.
When
GROUP BY
is present, or any aggregate functions are present, it is not valid for theSELECT
list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.
A certain other RDBMS is known to play dirty tricks here and allow this and pick arbitrary values...
You seem to want a list of unique patients that have commented, with the latest comment each. The simplest way in Postgres is with DISTINCT ON
:
SELECT DISTINCT ON (patient_id) *FROM commentsWHERE clinician_id = $1ORDER BY patient_id, created_at DESC NULLS LAST;
But this won't fly with SQLite - which should not be in the loop to begin with:
NULLS LAST
is only relevant if created_at
can be NULL:
Details for DISTINCT ON
: