Calculate Month Statistics

activerecord, group-by, postgresql, ruby-on-rails

Solution

Normally you'd left join to a calendar table (or `generate_series` in PostgreSQL) to get the missing months but the easiest thing with Rails would be to merge your results into a Hash of zeroes; something like this:

class Donation
  def self.by_month
    h = Donation.calculate(:sum, :amount, :conditions => { 
      :created_at => (Time.now.prev_year.all_year) }, 
      :order => "EXTRACT(month FROM created_at)", 
      :group => ["EXTRACT(month FROM created_at)"])
    Hash[(1..12).map { |month| [ month, 0 ] }].merge(h)
  end
end

then just call the class method, `h = Donation.by_month`, to get your results.

Problem

I have a donations table where I'm trying to calculate the total amount for each month. For months without without any donations, I'd like the result to return 0. Here's my current query: ``` Donation.calculate(:sum, :amount, :conditions => { :created_at => (Time.now.prev_year.all_year) }, :order => "EXTRACT(month FROM created_at)", :group => ["EXTRACT(month FROM created_at)"]) ``` which returns: ``` {7=>220392, 8=>334210, 9=>475188, 10=>323661, 11=>307689, 12=>439889} ``` Any ideas how to grab the empty months?

Original source