4

Given this pseudo-query:

Model.sum(:column, :condition => ['id = ?', id], order => "date DESC", :limit => X)

Here we have the problem that the sum obtained is not "limited" by the value X (it takes as sum all values on the column instead that the sum of the X (firsts) entries).

It seems that the limit is taken after calculating the aggregate func sum.

How can I get the sum only limited by the firsts X entries?

damoiser
  • 6,058
  • 3
  • 40
  • 66

2 Answers2

10

MurifoX's suggestion doesn't work because LIMIT applies only to the final result set, which has only one row (the sum). See this thread.

The simplest way to solve this is not to do the count in the database:

Model.where(:id => id).order('date DESC').limit(3).pluck(:column).compact.sum

This loads the values of column into Ruby and sums it there. Now, if the set of records is really enormous, this will be observably less efficient, just because all those values are getting loaded into your app's memory.

Edit: Added .compact - this will remove nils from the array before summing it (which causes an error).

The suggested method of doing this in SQL is using a subquery, like so:

SELECT SUM(subquery.column) FROM (
  SELECT * FROM models WHERE id = 5 LIMIT 3
) AS subquery;

There's not really an easy or straightforward way to turn this into an ActiveRecord query, since those expect to be selecting from a particular model's table.

MrTheWalrus
  • 9,670
  • 2
  • 42
  • 66
  • yep, finally this method works! :) but 'sum' raise an error if the data is not present (= nil) for some value in the limit (for example if limit = 30 and there are only 10 values), have you an idea how to solve this "inline"? – damoiser Mar 18 '13 at 17:37
  • 1
    @damoiser To ignore nil values, use `.compact.sum` instead of just `.sum` – MrTheWalrus Mar 18 '13 at 20:13
1

Did you tried to write the query the other way around? 

My_model.where(:id => id).order('date DESC').limit(X).sum(:column)

This way, you have filtered your model to only sum the narrowed values.

MurifoX
  • 14,991
  • 3
  • 36
  • 60
  • even in this manner it takes the sum of all entries. I have also tried to store it in a separate variable and then doing the sum but nothing change.. the only method that I see is to iterate manually the response and sum the values manually, but is it strange that there is no method that sum only a limited active_records results – damoiser Mar 18 '13 at 12:02
  • well, I have find a workaround (with date >= Z, where Z is "30 days ago") but I leave question open. It is strange that there is not a working solution with the limit constraint. – damoiser Mar 18 '13 at 13:43