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