Getting an Average from a Running Total

[jemsmom]

I’m using Crystal XI with Oracle 10g

The purpose of my report is to provide a count of the number of rooms running by half hour by day and then an average of the rooms running by date and by day of the week. The report runs for a month at a time and I have it grouped by Day of week and Date and Room.

The issue I’m having is if a room has two cases in the half hour time frame I’m returning a count of 2. If I use a running total I can get a room count of 1, but then I cannot get the average for the date and DOW.

My data looks like this for the 09:00 to 09:30 time column:

In Out Room Hour 9:00-09:30 Date
07:35 08:00 OR04 0 1/5/10
08:12 08:40 OR04 0 1/5/10
08:48 09:10 OR04 1 1/5/10
09:17 09:39 OR04 1 1/5/10
10:41 12:05 OR04 0 1/5/10
08:06 8:29 OR02 0 1/5/10
08:44 09:07 OR02 1 1/5/10
09:27 09:51 OR02 1 1/5/10
07:35 07:59 OR01 0 1/12/10
08:39 09:06 OR01 1 1/12/10
09:19 09:43 OR01 1 1/12/10
07:34 08:04 OR02 0 1/12/10
08:16 09:13 OR02 1 1/12/10
09:28 10:43 OR02 1 1/12/10
This is what I would like to see:
Total Rooms Running 1/5/10 2
Average Rooms Tuesday 2


Any ideas how to get the average of the running total result?

Thank you