From: Matthew Smith <mps@utas.edu.au>
To: pgsql-sql@postgresql.org
Subject: Help on a complex query (avg data for day of the week)
Date: Mon, 19 Dec 2005 16:57:48 +1100
User-Agent: KMail/1.5.3
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <200512191657.48139.mps@utas.edu.au>

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