Take 2. To allow for inserted OID's not messing with the running total,
you need to only cheque OID where the date is equal. So you're SQL is:
SELECT oid, *, (SELECT sum(amount) FROM checks WHERE x.thedate >
thedate OR (x.thedate = thedate AND x.oid >= oid ) AS total
FROM checks x
ORDER BY thedate,oid ;
Whew! I'm happy now :-)
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>
