Applying aggregate function SUM together with GROUP BY

Question

What is the best way to execute this query in Active Record / Ruby on Rails

SELECT sum(amount) as total_amount FROM payments GROUP BY person_id, product_id

I can't seem to chain sum with group method. Do I have to resort to find_by_sql here?

Answer 1

Something like:

Payment.group(:person_id).group(:product_id).sum(:amount)

should give you a hash whose keys are [person_id, product_id] arrays and whose values are the sums. You probably don't want to:

Payment.group('person_id, product_id').sum(:amount)

though, that might give you the right sums but the Hash's keys won't make much sense.

You can also say:

Payment.sum(:amount, :group => 'person_id, product_id')

but that will suffer the same brain damage in the keys as Payment.group('person_id, product_id').sum(:amount) does; however, an array for :group:

Payment.sum(:amount, :group => [ :person_id, :product_id ])

should produce the same Hash with Array keys as the double .group version. However, the :group form of sum is deprecated in Rails4 (thanks to Ganeshwara Herawan Hananda Put for noting this).

The above works fine in Rails3 and I see no reason that it wouldn't work in Rails4. I don't have a Rails4 setup handy so I can't verify that.