sql - column must appear in the GROUP BY clause or be used in an aggregate function -

sql - column must appear in the GROUP BY clause or be used in an aggregate function -

this question has reply here:

rails / postgres: “must appear in grouping clause or used in aggregate function” 3 answers

so have submissions, users, stories, , views tables in postgresql (among others) follows:

submissions ----------- user_id story_id body views ----------- user_id submissions_id users ----------- username

i have complicated find sql query returns submissions ordered whether or not user has seen (has entry user_id , submission_id in views table), , how many views has:

submissions.find_by_sql("select submissions.*, count(views.submission_id) views_count, (select count(*) views views.user_id=#{user_id} , views.submission_id=submissions.id grouping id) counted submissions left outer bring together views on views.submission_id=submissions.id submissions.story_id = #{id} grouping submissions.id order counted desc, views_count")

i able username each submission, should easy: bring together users table @ users.id=submissions.user_id , add together users.username select portion. however, when this, receive error

column "users.username" must appear in grouping clause or used in aggregate function

this shouldn't be, adding users grouping remove multiplesubmissions have same user, , have no need aggregate function.

and since apparently have state explicitly, i'm aware there many other questions asking this, , none of them reply issue.

when have group by clause, in systems restricted showing fields in group by clause in select statement. plus aggregating columns using count(), sum(), etc

try query wrapped around original query:

select * ( select top 100 percent submissions.id, count(views.submission_id) views_count, (select count(*) views views.user_id=#{user_id} , views.submission_id=submissions.id grouping id) counted submissions left outer bring together views on views.submission_id=submissions.id submissions.story_id = #{id} grouping submissions.id order counted desc, views_count ) innerquery left bring together submissions on submissions.id = innerquery.id

sql ruby-on-rails postgresql