Menu
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?
760
The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
Aggregations Can Be Filtered Using The HAVING Clause The alternate having is placed after the group by and allows you to filter the returned data by an aggregated column. Using having, you can return the aggregate filtered results!
This is because GROUP BY will only return unique results per group and the SELECT list can only consist aggregate functions or columns that are part of the GROUP BY clause. So depending on what you want to get, you can use different functions to get the optimal output.
The GROUP BY clause is often used with an aggregate function to perform calculations and return a single value for each subgroup.
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.
140
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
