I had some data that I wanted to aggregate by year for a flash graphing app called amCharts. Here was the SQL query I used to gather the data:

SELECT AVG(column1) AS column1, AVG(column2) AS column2 FROM table1 t1
JOIN table2 t2 ON t2.id = t1.tabel2_id
GROUP BY YEAR(t2.datetime_field)

Rather than using this sql in ActiveRecord via find_by_sql, I figured I’d make it pretty and use the built in find method. Here is what I came up with:

Table1.find(:all, :select => 't2_id, AVG(column1) as column1, AVG(column2) as column2', :joins => :t2, :group => 'YEAR(t2.datetime_field)')

The :select option allows you to define the attributes you want returned with the AR object. I’m using MySQL to do the averaging of the data via the AVG function. I would normally use the :include option for joins, but I couldn’t get it to work with this query, so the :joins option seemed to work well by passing it the association definition from the model. The :group option simply works the same as the SQL group by. Just pass it a sql fragment, in my case just the year field portion of the datetime field.