Message-ID: <43A928C2.8060901@archonet.com>
Date: Wed, 21 Dec 2005 10:04:50 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5 (X11/20051025)
MIME-Version: 1.0
To: Matthew Smith <mps@utas.edu.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Help on a complex query (avg data for day of the week)
References: <200512191657.48139.mps@utas.edu.au>
In-Reply-To: <200512191657.48139.mps@utas.edu.au>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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