MySQL to Postgres group_by error

Question

The following query works fine in MySQL (which is generated by rails):

SELECT
   sum(up_votes) total_up_votes 
FROM
   "answers" 
WHERE
   "answers"."user_id" = 100
ORDER BY
   "answers"."id" ASC

Yet it gives the following error in Postgres:

PG::GroupingError: ERROR:  column "answers.id" must appear in the GROUP BY clause or be used in an aggregate function

Edit: updated query. And here is the Rails model where this query is performed:

class User < MyModel
  def top?
    data = self.answers.select("sum(up_votes) total_up_votes").first
    return (data.total_up_votes.present? && data.total_up_votes >= 10)
  end
end

Perhaps there is an alternative method which tells rails there is only one row in the result, and therefore not to add an order by? — IMSoP
– IMSoP, Commented May 14, 2014 at 18:01
Considering how many Rails people use Postgres, I sure hope there is a way. Never had this problem with MySQL. — user3188544
– user3188544, Commented May 14, 2014 at 18:04
Looks like you should be able to use self.answers.sum(...) or .calculate(...) for this case: api.rubyonrails.org/classes/ActiveRecord/… (I don't use Ruby on Rails, so can't confirm) — IMSoP
– IMSoP, Commented May 14, 2014 at 18:08

Gordon Linoff · Accepted Answer · 2014-05-14 17:34:15Z

2

The query may execute in MySQL, but I doubt that it "works fine". It returns only one row, so the ordering is meaningless.

Your question is vague on what you actually want to do, but this should produce the same results as your query, in both databases:

SELECT sum(up_votes) as total_up_votes
FROM answers
WHERE user_id = 100;

answered May 14, 2014 at 17:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3188544 Over a year ago

I updated the question. The queries are generated by Rails automatically. And if I remove the ordering, Rails still tacks on ordering by the id by default. This still doesn't work in Postgres.

Gordon Linoff Over a year ago

@user3188544 . . . If you add a group by user_id to the query, that might convince Rails not to do silly things with the order by. Perhaps it is confused at an aggregation query without a group by. (Note: total speculation on my part.)

Collectives™ on Stack Overflow

MySQL to Postgres group_by error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related