Brett Parker wrote:
Yup, that's how that's done... Here's the *MAGIC* ;)
create view testwithrunningtotal as select date,description, amount,(select sum(amount) from test as test2 where test2.date <= test.date) as balance from test order by date;
select * from testwithrunningtotal;
What sort of performance would you expect from this sort of query in a large table? If I'm reading it right it isn't really generating a running total, so much as calculating a complete sum for each transaction. Assuming that several years of records will be kept, this could get quite slow?
[That's a genuine question, not a snide remark - I'm interested to know whether PostgreSQL can optimise this somehow, or is just fast enough to do it, or if I'm reading it wrong in the first place?]
To Chris: is there a reason for generating this at the query level, rather than the application level? If you are still playing with PHP this should be trivial to calculate after you have the data from PostgreSQL. Two queries (one to calculate the total of all transactions before the start date of the report, and another to bring back the transactions for the report itself) seems to me that it ought to be quicker as the number of stored transactions grows.
Still, I like the ingenuity of Brett's query (but as I do most of my db work in mySQL I have to look up to real database programmers with awe :-)