Select, group and sum results from database

Question

Hello

I have a database with some fields I'd like to sum. But that's not the big problem, I want to group those fields by the month they were created. ActiveRecord automaticaly created a field named "created_at". So my question; how can I group the result by month, then sum the fields for each month?

Updated with code

@hours = Hour.all(:conditions => "user_id = "+ @user.id.to_s, 
                  :group => "strftime('%m', created_at)",
                  :order => 'created_at DESC')

This is the code I have now. Managed to group by month, but doesn't manage to sum two of my fields, "mins" and "salary" which I need to sum

Answer 1

I don't know if there's a SQL query you use to do it (without changing your current table structure). However, you do it with some lines of code.

records = Tasks.find(:conditions => {..})
month_groups = records.group_by{|r| r.created_at.month}
month_groups.each do |month, records|
  sum stuff.. blah blah blah..
end

I saw this link on the right side of this question. I assume other databases, besides MySQL have similar functions.

http://stackoverflow.com/questions/937652/mysql-select-sum-group-by-date

Answer 2

You can use active record calculations to do this. Some example code might be

Model.sum(:column_name, :group => 'MONTH("created_at")')

Obviously with the caveat MONTH is mysql specific, so if you were developing on an SQLite database this would not work.

Answer 3

Fixed it by using :select when getting the query, inputing selects manually

@hours = Hour.all(:conditions => "user_id = "+ @user.id.to_s,
                  :select => "created_at, SUM(time) time",
                  :group => "strftime('%m', created_at)",
                  :order => 'created_at DESC')