Rails 4: Group by Month

Question

In Rails/Ruby, how do I group by and aggregate a measure by month, based on a date column in the table.

I have tried the following technique from Railscasts.

http://railscasts.com/episodes/29-group-by-month

So far, my code look like this

result = result.where(transaction_date: start..Date.current)
result = result.select(:transaction_date, :quantity)
result.group_by { |t| t.transaction_date.beginning_of_month }

I guess, I need a Ruby equivalent of SQL GROUP BY to rollup the quantity.

The SQLite3 version of what I am looking for is:

select strftime('%m', transaction_date)
     , sum(quantity)
from transactions
group by strftime('%m', transaction_date)

Answer 1

So this is a good example of why you should use the same database in dev and prod, because you really want to do this in SQL which will be different for the two databases you've specified. I'll give you the Postgres version.

Assuming your model is called 'Entity' and you're summing an attribute called quantity, you're going to want to do something like this:

Entity
  .select("DATE_TRUNC('month', created_at) AS month, SUM(quantity) AS total_quantity")
  .group('month')

The resulting Entity objects will have virtual attributes 'month' and 'total_quantity' which you can use for your reporting.