# Getting an average of a column per day **URL:** \ **Category:** Rails\ **Created:** [August 13, 2009, 4:41pm UTC](https://www.ruby-forum.com/t/getting-an-average-of-a-column-per-day/173709 "2009-08-13T16:41:04Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![ryank](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/bc79bd/32.png) [@ryank](https://www.ruby-forum.com/u/ryank)\ **Post date:** [August 13, 2009, 4:41pm UTC](https://www.ruby-forum.com/t/getting-an-average-of-a-column-per-day/173709/1 "2009-08-13T16:41:04Z")
I’m trying to generate a report for the average number of calls a person makes per-day. The following generates the correct list, but if a user didn’t make any calls a certain day it doesn’t show up. from\_date = Time.now.at\_beginning\_of\_month.to\_s(:db) to\_date = Time.now.tomorrow.to\_s(:db) ave\_calls = @current\_user.calls.average(:num\_of\_calls, :group =\> “DATE(calls.start\_time\_stamp)”, :conditions =\> [“calls.created\_at BETWEEN ? AND ?”, from\_date, to\_date]) What is the best way of doing this? An example I have right now, I have data from August 3rd til Today, no calls were made August 2nd or August 1st. The list comes back and starts at the 3rd. ---
**Author:** ![ryank](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/bc79bd/32.png) [@ryank](https://www.ruby-forum.com/u/ryank)\ **Post date:** [August 13, 2009, 6:59pm UTC](https://www.ruby-forum.com/t/getting-an-average-of-a-column-per-day/173709/2 "2009-08-13T18:59:48Z")
Ryan K. wrote: > I’m trying to generate a report for the average number of calls a person > makes per-day. The following generates the correct list, but if a user > didn’t make any calls a certain day it doesn’t show up. Why would a day with no calls appear? Its just reporting the DB data in an aggregated format. Merge the returned ave\_calls with a list built out of an iteration of from\_date to to\_date by day to fill the gaps. ---
**Author:** ![ryank](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/bc79bd/32.png) [@ryank](https://www.ruby-forum.com/u/ryank)\ **Post date:** [August 13, 2009, 7:46pm UTC](https://www.ruby-forum.com/t/getting-an-average-of-a-column-per-day/173709/3 "2009-08-13T19:46:16Z")
Ar Chron wrote: > Ryan K. wrote: > > > I’m trying to generate a report for the average number of calls a person > > makes per-day. The following generates the correct list, but if a user > > didn’t make any calls a certain day it doesn’t show up. > > Why would a day with no calls appear? Its just reporting the DB data in > an aggregated format. > > Merge the returned ave\_calls with a list built out of an iteration of > from\_date to to\_date by day to fill the gaps. call\_aves = (Time.now.at\_beginning\_of\_month…Time.now.tomorrow).map{|dt| [dt, ave\_calls[dt.strftime “%Y-%m-%d”] || 0]} At first, I just stuck call\_aves into the value for Open Flash Charts, and it broke it, kind’ve looked like it went into an infinite loop. So I went into my debugger to see what was happening. irb(#ReportsController:0x47b7524):008:0\> call\_aves[0] =\> [Sat Aug 01 00:00:00 -0400 2009, 0] As I go up in the array, i notice it’s only incrementing by seconds, not by days. For example: irb(#ReportsController:0x47b7524):009:0\> call\_aves[30] =\> [Sat Aug 01 00:00:30 -0400 2009, 0] How do I get this to go up by the day, not the second? ---
**Author:** ![ryank](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/bc79bd/32.png) [@ryank](https://www.ruby-forum.com/u/ryank)\ **Post date:** [August 13, 2009, 10:30pm UTC](https://www.ruby-forum.com/t/getting-an-average-of-a-column-per-day/173709/4 "2009-08-13T22:30:54Z")
> How do I get this to go up by the day, not the second? Change your grouping function in the SQL, rather than DATE(calls.start\_time\_stamp) can you use just the day of the month portion? Perhaps DAYOFMONTH(calls.start\_time\_stamp) (or just DAY(calls.start\_time\_stamp)) as long as you’re selecting within a month, like start of month to current date. ---
**Author:** ![ryank](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/bc79bd/32.png) [@ryank](https://www.ruby-forum.com/u/ryank)\ **Post date:** [August 14, 2009, 12:14am UTC](https://www.ruby-forum.com/t/getting-an-average-of-a-column-per-day/173709/5 "2009-08-14T00:14:36Z")
On Aug 13, 6:46 pm, Ryan K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > As I go up in the array, i notice it’s only incrementing by seconds, not > by days. For example: > > irb(#ReportsController:0x47b7524):009:0\> call\_aves[30] > =\> [Sat Aug 01 00:00:30 -0400 2009, 0] > > How do I get this to go up by the day, not the second? Well if you use dates rather than times that happens for free (since + 1 for a date means + 1 day), if not then you just need to put some time values in an array, incrementing by a day at a time ( + 1.days or using advance for example) Fred