Average over time

Hello all,

   I have a table ("transactions") which has the following columns:

      id (serial)
      dt (date)
      amount (float)
      cat_id (int)
      sup_id (int)

   Each time I purchase something I record it in my database. I like to
extract statistics. I have - using some earlier help from group - been
able to get some neat data.

   But then I decided to get creative. I wanted to see how an "average
expense per day/week/month" line diagram would look like. I wrote a
function to do this for days, but I'm doing it using a for loop (I'm
writing this in Python, but that's not really important). Pseudocode:

   for idate in range(firstdate, lastdate):
      query("select avg(foo.asum) from (select dt,sum(amount) as asum
where dt >= '%s' AND dt <= '%s' group by dt) AS foo)" % (firstdate, idate)

   Well, as you gather, this will perform n unique queries, where n is
the number of days in the date range. It's actually pretty fast, but I
would *like* to get a table which looks something like:

   dt         | avg_asum
   -----------+------------
   2007-01-01 | 1024
   2007-01-02 | 962

   ...etc. Obviously, the avg_asum is the average for asum up to the dt
column's date. My gut feeling is that this can not be done -- but I
don't know why. Is it possible?


--
Kind regards,
Jan Danielsson