CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
winter sun
winter sun

Reputation: 582

Rails PostgreSQL problems with order statement

Hi I changed my database from mySql to PostgreSQL and get an error every time I use query with :order statement

For example the following code works perfectly in MySQL 

Hour.sum("working_hours",:conditions=>['project_id=? AND reported_date=?',project,h.reported_date],:order=>"reported_date

But gives me an error in PostgreSQL

PGError: ERROR: column "hours.reported_date" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...rted_date='2010-10-06 00:00:00.000000') ORDER BY reported_d..
: SELECT sum("hours".working_hours) AS sum_working_hours FROM "hours" WHERE (project_id=1 AND reported_date='2010-10-06 00:00:00.000000') ORDER BY reported_date

If I delete the order statement then the query works ok
I will most appreciate any help on this subject

Upvotes: 1

Views: 463

Answers (1)

Ariejan
Ariejan

Reputation: 11069

PostreSQL is stricter to the SQL standard than MySQL is.

SQL states that if you ORDER by a column, that column must be SELECTed and appear in the GROUP BY clause.

Try this:

Hour.sum("working_hours",:conditions=>['project_id=? AND reported_date=?',project,h.reported_date], :order=>"reported_date", :group_by => "working_hours"

Upvotes: 2

Related Questions