titan titan - 5 months ago 25
SQL Question

Raw pg GROUP BY query in rails application

I have this very simple

SQL
query that i am trying to execute in my
rails
console.

SELECT name, manual_score FROM objectives GROUP BY manual_score


But it throws an error which is:

ActiveRecord::StatementInvalid: PG::GroupingError: ERROR: column "objectives.name" must appear in the GROUP BY clause or be used in an aggregate function


I have tried by prepending table name with columns but the error remains. Any help would be appericiated. Thanks!

Answer

The problem is that you are listing a column that is not "grouped". You should add name to GROUP BY or remove it from the select.

SELECT name, manual_score FROM objectives GROUP BY name, manual_score
-- OR
SELECT manual_score FROM objectives GROUP BY manual_score
-- OR
SELECT COUNT(name), manual_score FROM objectives GROUP BY manual_score

Why do you have to add the column into your group by or use an aggregate function? Imagine you have the following data:

name       |  manual_score
one        |  1
two        |  1
three      |  2

Now, try to group elements by manual_score and think how to show name column that corresponds to manual_score=1.

Comments