Raw pg GROUP BY query in rails application

Question

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 1

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.