CODEWITHSUNDEEP
ruby-on-railsruby
Geordee Naliyath
Geordee Naliyath

Reputation: 1859

Rails 4: Group by Month

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)

Upvotes: 8

Views: 7303

Answers (1)

Peter Goldstein
Peter Goldstein

Reputation: 4545

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.

Upvotes: 7

Related Questions