Rails / Postgres: “must appear in the GROUP BY clause or be used in an aggregate function”

Question

I'm using this method:

  def self.lines_price_report(n)
    Income.group('date(filled_at)').having("date(filled_at) > ?", Date.today - n).sum(:lines_price)
  end

I'm getting this error in Heroku:

PG::Error: ERROR:  column "incomes.filled_at" must appear in the GROUP BY clause 
or be used in an aggregate function

How can I fix this? Thank you.

Executed query:

SELECT SUM("incomes"."lines_price") AS sum_lines_price, date(filled_at)
AS date_filled_at FROM "incomes"
HAVING (date(filled_at) > '2012-12-04')
GROUP BY date(filled_at) ORDER BY filled_at ASC

Expected result

[["2012-12-04", SUM_FOR_DATE], ["2012-12-05", SUM_FOR_DATE], ...]

Answer 1

Your mistake was to use filled_at in order by probably in default scope.

You can fix it using unscoped to eliminate default scopes:

Income.unscoped
 .group('date(filled_at)')
 .having("date(filled_at) > ?", Date.today - n)
 .sum(:lines_price)

or

Income.unscoped
   .group('date(filled_at)')
   .having("date(filled_at) > ?", Date.today - n)
   .sum(:lines_price)
   .order('date(filled_at) ASC')

but I think that better will be to use where instead of having

Income.unscoped
  .where("date(filled_at) > TIMESTAMP ?", Date.today - n)
  .group('date(filled_at)')
  .sum(:lines_price)
  .order('date(filled_at) ASC')

SQLFiddle

You have to be careful about using TIMESTAMP because 2012-12-04 will become 2012-12-04 00:00:00 so if you don't want this day in result use Date.today - (n - 1)

If you create index on filled_at column

 create index incomes_filled_at on incomes(filled_at);

migration:

 add_index :incomes, :filled_at

and you have a lot of data in this table index will be used in filtering. So query should be much faster.

So just write both and test which is faster (you have to create index on filled_at if you don't have one).

Answer 2

I guess this is because you use date(filled_at) in GROUP BY but just filled at in ORDER. As I guess order is taken from default scope you need to overwrite it by reorder. I would suggest:

Income.sum(:lines_price).
    group('date(filled_at)').
    having("date(filled_at) > ?", Date.today - n).
    reorder("date(filled_at) ASC")