Average values by group

Question

I have two tables in my web app: one is for Donors (called "donors") and the other is for Donation Amounts (called "donations). When you click on a donor, you can see all of their donations.

I'm trying to average values associated with a particular date, for a particular charity. For example, if these records exist for Donor A:

        *Donor A*

   Date       Donation Amount
05/04/2013          30
05/04/2013          40
05/05/2013          15
05/05/2013          75

I'd like the system to also calculate and display that the average donation amount for 05/04/2013 was 35 and the average donation amount for 05/05/2013 was 45.

Currently I've tried using the group attribute in my donor model:

def self.average_donateperdate
    Donation.average(:donateamount, conditions: ["donor_id = ?", @donor], group: "donatedate")
end

This doesn't seem to work. Since I'm new to Rails, I'm not entirely sure whether this is the right way to do it or not. There are a few other posts that touch on this topic, but none that have helped me solve the issue. Any help would be greatly appreciated!

Answer 1

The simplest syntax to do this is:

@donor.donations.group(:donatedate).average(:donateamount)

This will return a hash in the format { 'donatedate' => 'average donateamount' }.

Naturally, this assumes you have a has_many :donations association on your Donor model. A reusable method would look like:

def average_donation_by_date
  donations.group(:donatedate).average(:donateamount)
end

And you can now simply call:

@donor.average_donation_by_date

To see this visually, you can call this in your controller:

@average_donations = @donor.average_donation_by_date.to_a.sort_by(&:first)

And your view might contain something like this:

<table>
  <thead>
    <tr>
      <th>Date</th>
      <th>Average Donation</th>
    </tr>
  </thead>
  <tbody>
    <% @average_donations.each do |date, amount| %>
      <tr>
        <td><%= date.strftime('MM/dd/yyyy') %></td>
        <td><%= amount %></td>
      </tr>
    <% end %>
  </tbody>
</table>

Reference

Rails api - calculate grouped values