CODEWITHSUNDEEP
ruby-on-rails-3
amit_saxena
amit_saxena

Reputation: 7614

Get total statistics (sum of a db column) of a model for each month for the last 1 year

I have a Payments model that stores the payment_date and amount. I want to get the total payment done each month for the last one year.

I started by getting all the payments done in the last 12 months. Now how can I group them for each month and get the total amount for the month. I just need the the following two fields in the result set: "month and year" and "total_amount". I need this data to construct a graph for month wise spending.

I am using rails 3.

Upvotes: 0

Views: 1011

Answers (1)

David
David

Reputation: 7303

Update

The following query returns grouped Payment objects, so you will need to extract the fields you need, something like this:

@payments = Payment.select("SUM(amount) as total_payment, MONTH(payment_date) as month, YEAR(payment_date) as year").group("MONTH(payment_date), YEAR(payment_date)") 
@data = @payments.map {|payment| [payment.amount, payment.month, payment.year]}

and then extract the values you need.

Upvotes: 1

Related Questions