RE: [ALUG] Running total from a database table - how?
From: Chris Green Sent: 22 November 2004 13:40
I'm steadily progressing with my conversion of my Access accounting system to a Linux based system.
I've moved from using mysql to postgresql which has made a few things easier by having VIEWs in the database.
Rekall seems to have most of what I need on the forms and reports front except for one thing - I can't see how to implement a 'running total' column.
The Access97 report tool has a specific 'running total' column type which does exactly what I need. Rekall doesn't have anything like this (that I can see).
Has anyone got any bright ideas of how I can do this, it's rather vital on things like a Bank Reconciliation report to enable me to check my accounts against the bank statement.
-- Chris Green (chris@areti.co.uk)
can you give a specific example as 'running total' covers a lot of ground :o) Regards, Keith ____________ Whatever thou sayest of God is untrue. - Meister Eckhart
On Mon, Nov 22, 2004 at 03:59:14PM -0000, Keith Watson wrote:
From: Chris Green Sent: 22 November 2004 13:40
I'm steadily progressing with my conversion of my Access accounting system to a Linux based system.
I've moved from using mysql to postgresql which has made a few things easier by having VIEWs in the database.
Rekall seems to have most of what I need on the forms and reports front except for one thing - I can't see how to implement a 'running total' column.
The Access97 report tool has a specific 'running total' column type which does exactly what I need. Rekall doesn't have anything like this (that I can see).
Has anyone got any bright ideas of how I can do this, it's rather vital on things like a Bank Reconciliation report to enable me to check my accounts against the bank statement.
-- Chris Green (chris@areti.co.uk)
can you give a specific example as 'running total' covers a lot of ground :o)
Yes, of course, I want a report that looks like:- Date Description Amount Balance 01/09/04 Initial balance +1000.00 1000.00 03/09/04 Stamps -14.58 985.42 06/09/04 Network Card -9.99 975.43 12/09/04 Interest +0.10 975.53 etc. It's a little more complicated than the above but that shows the essentials. (I don't guarantee my arithmetic is correct) The Date, Description and Amount columns are directly from a database table. The Balance column needs to ba calculated as the report is created. -- Chris Green (chris@areti.co.uk) "Never ascribe to malice, that which can be explained by incompetence."
On Mon, Nov 22, 2004 at 04:17:56PM +0000, Chris Green wrote:
Yes, of course, I want a report that looks like:-
Date Description Amount Balance 01/09/04 Initial balance +1000.00 1000.00 03/09/04 Stamps -14.58 985.42 06/09/04 Network Card -9.99 975.43 12/09/04 Interest +0.10 975.53
date | description | amount | balance ----------------------------+------------------+---------+--------- 2004-11-22 16:39:52.046068 | Starting Balance | 1000.00 | 1000.00 2004-11-22 16:40:11.58727 | Booze | -50.00 | 950.00 2004-11-22 16:40:30.539512 | Poker Winnings | 50.00 | 1000.00 2004-11-22 16:40:59.067902 | CD | -10.99 | 989.01
It's a little more complicated than the above but that shows the essentials. (I don't guarantee my arithmetic is correct)
The Date, Description and Amount columns are directly from a database table. The Balance column needs to ba calculated as the report is created.
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; OK - so it's not pretty, but it does appear to work ;) Obviously, if you're not really using timestamps, like I was, and you can *assume* that your IDs are in the right order, then sort by those for the subselect. If you need to be able to 'mess' with this, then you'll also need to set up some insert and delete rules on the view, probably ;) Cheers, -- Brett Parker web: http://www.sommitrealweird.co.uk/ email: iDunno@sommitrealweird.co.uk
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 :-) -- Mark Rogers, More Solutions Ltd :: Tel: 0845 45 89 555
On Mon, Nov 22, 2004 at 05:50:05PM -0000, Mark Rogers wrote:
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?
With a large dataset, I'd expect it to start getting quite slow. But, ofcourse, you could split the accounts in to monthly tables to assist in the process, and write a slightly more sick and twisted query to generate the results, not sure how off the top of my head, though ;)
[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?]
You're reading it right, unfortunately, I could probably work out a better query, it's just that one rolled off the top of my head quickest :)
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 :-)
If you forget about creating the view, and just use it as a select, it should even work in mysql. Of course, mysql should be getting views RSN :). Cheers, -- Brett Parker web: http://www.sommitrealweird.co.uk/ email: iDunno@sommitrealweird.co.uk
On Mon, Nov 22, 2004 at 05:50:05PM -0000, Mark Rogers wrote:
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?
The performance will probably not be very clever but since my year's accounts for last year only have around 350 transactions it'll probably perform fine for me.
[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.
There's no reason for doing at the query level except that Rekall, the front end I'm using, doesn't seem to be capable of using it. I'm not using PHP because it's not really capable of doing the multi-line data entry forms I want (which Rekall can do easily).
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 :-)
It's one of the reasons I moved from mysql to postgresql, the ability to use VIEW. (I know it's coming in mysql 5 but it's not really here yet). -- Chris Green (chris@areti.co.uk) "Never ascribe to malice, that which can be explained by incompetence."
On Mon, Nov 22, 2004 at 04:47:35PM +0000, Brett Parker wrote:
On Mon, Nov 22, 2004 at 04:17:56PM +0000, Chris Green wrote:
Yes, of course, I want a report that looks like:-
Date Description Amount Balance 01/09/04 Initial balance +1000.00 1000.00 03/09/04 Stamps -14.58 985.42 06/09/04 Network Card -9.99 975.43 12/09/04 Interest +0.10 975.53
date | description | amount | balance ----------------------------+------------------+---------+--------- 2004-11-22 16:39:52.046068 | Starting Balance | 1000.00 | 1000.00 2004-11-22 16:40:11.58727 | Booze | -50.00 | 950.00 2004-11-22 16:40:30.539512 | Poker Winnings | 50.00 | 1000.00 2004-11-22 16:40:59.067902 | CD | -10.99 | 989.01
It's a little more complicated than the above but that shows the essentials. (I don't guarantee my arithmetic is correct)
The Date, Description and Amount columns are directly from a database table. The Balance column needs to ba calculated as the report is created.
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;
OK - so it's not pretty, but it does appear to work ;) Obviously, if you're not really using timestamps, like I was, and you can *assume* that your IDs are in the right order, then sort by those for the subselect.
If you need to be able to 'mess' with this, then you'll also need to set up some insert and delete rules on the view, probably ;)
Coo, I had sort of thought about an approach like that but hadn't really made the attempt. Thanks very much for pointing me in the right direction! That offloads yet another bit from Rekall. -- Chris Green (chris@areti.co.uk) "Never ascribe to malice, that which can be explained by incompetence."
participants (4)
-
Brett Parker -
Chris Green -
Keith Watson -
Mark Rogers