Matthew Smith wrote:
I want to form a query that returns the average total usage for each day of
the week, eg:
[snip]
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.
1. Calculate how many data-points each day represents
2. Sum the days you do have data for
3. Left-join #1 to #2 so you can calculate the average.
I'm guessing for #1 you'd rather not have 7 UNIONs, so you might find
generate_series() a useful function. See Ch 9.18. Set Returning Functions.
HTH
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
