Geek Answers Handbook

Rails / Postgres: "should appear in a GROUP BY clause or used in an aggregate function"

I am 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 get 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? Thanks.

Completed request:

 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], ...]
+4
source share
3 answers

Your mistake was to use fill_at in the order, probably in the default area.

You can fix this using unscoped to exclude 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 it's better to use where instead

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

SQLFiddle

You should be careful using TIMESTAMP, because 2012-12-04 will become 2012-12-04 00:00:00, so if you do not want this day to be used in the results of Date.today - (n - 1)

If you create an index in the fill_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, the index will be used when filtering. Therefore, the request should be much faster.

So just write tests that are faster (you need to create an index on fill_at if you don't have one).

+6
source

I assume this is because you are using date(filled_at) in GROUP BY, but just filled at in ORDER. Since I assume that the order is taken from the default scope, you need to rewrite it to reorder . I would suggest:

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

If you want to use Group By on PostgreSQL, the selection option must be required for the group.

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

More articles:


All Articles