Re: Help on a complex query (avg data for day of the week)

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