Hello,
I have a table containing a timestamp and data usage fields (among others).
This table stores amounts of data usage and the times then the data was used,
eg:
time | data
------------------------+----------2005-03-26 09:32:43+11 | 162
I want to form a query that returns the average total usage for each day of
the week, eg:
day | avg_usage
--------+--------------------- 0 | 35684624.000000000 1 | 103344529.000000000 2 | 105899406.000000000
3| 21994539.000000000 4 | 113045173.000000000 5 | 110675115.000000000 6 | 8791397.0000000000
(7 rows)
To get this info, I am using the following query:
select dow as day, sum(sum_data)/count(dow) as avg_usage from
(select extract('dow' from date_trunc('day', time)) as dow, sum(data) as
sum_data
from datalog where time >= '2005-09-11' and time <= '2005-09-25' group by dow)
as avg_data_per_day group by day;
This works well, assuming that there is at least one entry in the table for
each day in the time period.
The problem comes when there are days where no data is logged. In my example,
the total data for each day of the week is divided by the number of the days
found. As there are exactly 2 of each day of the week between 2005-09-11 and
2005-09-25, we should hope to divide each total by 2. but if there is no data
logged for the 14th, then the total for wednesdays would be divided by 1. I
want it to be the sum of the 2 days divided by 2: (101994539 + 0)/2.
Is there a better way to do this? Or does anyone have any suggestions on the
best way to insert the missing dates into my query?
Any help would be great!
Thanks,
Matthew Smith