Rails PostgreSQL problems with order statement

Question

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

Ariejan · Accepted Answer · 2010-11-02 14:50:42Z

2

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"

answered Nov 2, 2010 at 14:50

Ariejan

11.1k6 gold badges45 silver badges40 bronze badges

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

2 Comments

Brett Bender Over a year ago

This query actually returns an ordered hash of sums, grouped by reported_date. If he wanted a sum of all working hours, this will not give him the correct answer.

winter sun Over a year ago

PGError: ERROR: column "hours.reported_date" must appear in the i tried it but i still get an error GROUP BY clause or be used in an aggregate function LINE 1: ...:00:00.000000') GROUP BY working_hours ORDER BY reported_d...

Collectives™ on Stack Overflow

Rails PostgreSQL problems with order statement

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