ruby on rails - Average all values for a each field date -

i'm trying build web app analyzes survey results movies, uploaded system manually administrator. results uploaded based on date survey fielded. there number of different categories surveyed , each respondent inputs number response (from 1-7) each category, each movie. have set system on movies index page, can select movie , bring specific movie's show page on site. i'd able have system display averages of survey results (by category) based on each field date, in table.

example raw results input admin:

field date: 03/23/2013, interesting:5, trendy: 6, funny: 3 field date: 03/23/2013, interesting:6, trendy: 6, funny: 7 field date: 03/29/2013, interesting:2, trendy: 4, funny: 3 

example of want output in table:

field date: 03/23/2013, interesting:5.5, trendy: 6, funny: 5 field date: 03/29/2013, interesting:2, trendy: 4, funny: 3 

here schema:

create_table "movies", :force => true |t|  t.string   "name"  t.string   "category"  t.date     "field" end  create_table "results", :force => true |t|  t.string   "name"  t.date     "field"  t.integer  "movie_id"  t.integer  "interesting"  t.integer  "trendy"  t.integer  "funny" end 

here table in movies show page:

<% @results.order('field desc').each |resultz| %>   <tr class="tableline" >     <td class="tableline"><%= resultz.field %></td>     <td class="tableline"><%= resultz.interesting %></td>     <td class="tableline"><%= resultz.trendy %></td>     <td class="tableline"><%= resultz.funny %></td>   </tr> 

currently shows raw values uploaded admin. think need use average method along :group option, reason when try out keeps giving me errors. don't think date_trunc works because i'm using sqlite3, though i'm not sure if that's right reasoning.

any appreciated (even linking appropriate documentation)! thanks!

i think sql query need execute in controller this:

select field, avg(interesting), avg(trendy), avg(funny) results movie_id = <movie_im_at_show_page_for> group field 

in rails this:

@results = results.find(select: "field, avg(interesting), avg(trendy), avg(funny)",  conditions: ["movie_id = ?", <movie_im_at_show_page_for>], group: "field") 

you'll have replace <movie_im_at_show_page_for> actual id. i'd think @movie.id in controller.