Re: Running Totals and other stuff....

Oh bugger, that doesn't work.  I tested it with a primary key (cheque
number), that worked, then I tested the AND oid, and managed to totally
confuse myself.  Apologies Jerry.

Adding a cheque number primary key would work tho'

Alan Graham

On Tue, 2004-06-01 at 20:40, Levan, Jerry wrote:
> Humpfff...Last night I tried posting this and found that
> dynamic IP's are now prevented from posting to the list...
>
> Did I miss the announcement?
>
> ************************************
>
> Hi,
>
> I keep all of my financial data in Postgresql ( 7.4.2).
> My "Check" register records deposits, withdrawals (as amount) , date,
> category and other stuff.
>
> The following sorta works...
>
> SELECT oid, *, (SELECT sum(amount) FROM checks  WHERE x.thedate >=
> thedate ) AS total
>     FROM checks x
>     ORDER BY  thedate,oid ;
>
> The problem is that all transactions on the same date get the total of
> all
> transactions for that date, so the resulting table is only "sorta" a
> running total.
>
> If I change the rascal to look like
> SELECT oid, *, (SELECT sum(amount) FROM checks  WHERE x.oid >= oid )
> AS total
>     FROM checks x
>     ORDER BY  thedate,oid ;
>
> I get the right results, but this relies on the fact the oids in the
> check table are currently *sorted* (when the table is sorted by
> thedate)
> at least it appears that way via
> a very brief inspection.... I suspect if I deleted a record and added
> a record the oids would get out of sequence.
>
> Is there a slick way to tell if a column (say the oids column) is in
> "sorted"
> order when the table is sorted by date?
>
> Assuming the oids get out of wack with respect to the date, is it
> possible
> to easily construct a table of the checks sorted by date and then
> "glue on"
> a column of ascending integers so the running total sql statement will
> function properly?
>
> Jerry
>
--
Alan Graham <alan.graham@infonetsystems.com.au>