How to perform single sql query to sum columns from grouped results in activerecord?

Question

I have two models - Parent and Params, where parent has_many params

Currently, my methods looks like (not that it is under Parent class):

def total_sum
  params.select(
    'params.*, (
      SUM(mono_volume_annular) + 
      SUM(day_volume_annular) + 
      SUM(night_volume_annular) + 
      SUM(exclusive_volume_annular)
    ) AS summed_volume_annular'
  ).group('params.id').sum(&:summed_volume_annular)
end

How can I improve this SQL query to get rid of .sum(&:summed_volume_annular) method call?

If I'll try to summarize it without converting :summed_volume_annular to proc, I'll get this error: ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR: column "summed_volume_annular" does not exist

An interesting note - If I'll try to save the result of this query

params.select('(
    SUM(mono_volume_annular) + 
    SUM(day_volume_annular) + 
    SUM(night_volume_annular) + 
    SUM(exclusive_volume_annular)
  ) AS summed_volume_annular'
)

I will get this object [#<Parameter:0x00000009f1c7d8 id: nil>]

Strange that summed_volume_annular is absent...

if you are using `group('params.id')` then why you are using `sum` inside query because your group always have one record — Vishal JAIN, May 20 '16 at 12:27
@VishalJAIN because I need to summarize multiple columns into one. I called it `summed_volume_annular`. I use multiple sum() functions because of [this](http://stackoverflow.com/questions/22369336/mysql-sum-multiple-columns/22369527#comment42560192_22369527) — Danny Ocean, May 20 '16 at 14:20

Diego · Accepted Answer · 2016-05-20T14:36:16.987

1

I would do something like this:

def total_sum
  columns_to_sum = %w(mono_volume_annular day_volume_annular night_volume_annular
                      exclusive_volume_annular)
  sum = columns_to_sum.map{ |col| "COALESCE(#{col}, 0)" }.join(' + ')
  Params.sum(sum)
end

edited May 20 '16 at 14:36

answered May 20 '16 at 13:44

Diego

848
7
16

I changed the example, hope it helps :) – Diego May 20 '16 at 14:29
it almost what I was looking for. Instead of summarizing all of the `Params` records I needed to summarize only associated with `parent` object. I changed `Params` to `params` and thats it. Thanks! – Danny Ocean May 20 '16 at 16:55

How to perform single sql query to sum columns from grouped results in activerecord?

1 Answers1