CODEWITHSUNDEEP
rubydatabaseruby-on-rails-3postgresql
Christian
Christian

Reputation: 859

rails postgreSQL cannot get this query to work (SUM, Group Order)

This query work when I try it in SQLite:

Transaction.where(:paid => true).select("created_at, SUM(amount) amount").group("DATE(created_at)").order('created_at')

But when I run it with postgreSQL it doesn'y work. Heres the error message:

ActiveRecord::StatementInvalid: PGError: ERROR:  column "transactions.created_at" must appear in the GROUP BY clause or be used in an aggregate function : SELECT created_at, SUM(amount) as amount FROM "transactions" WHERE ("transactions"."paid" = 't') GROUP BY DATE(created_at) ORDER BY created_at

Anyone who can help me?

Thanks in advance

Upvotes: 1

Views: 937

Answers (2)

Denis de Bernardy
Denis de Bernardy

Reputation: 78413

You're selecting created_at, sum(amount), ordering by created_at, but are grouping by date(created_at). The latter will disallow the use of anything but the grouped by fields and aggregates except in the join/where clause.

To fix, either group by created_at, or select and order by date(created_at) instead of created_at.

Upvotes: 1

dawebber
dawebber

Reputation: 3553

You have to either use DATE(created_at) in the select clause, or use created_at in the group by clause.

Upvotes: 1

Related Questions