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