Re: Running Totals and stuff...

> -----Original Message-----
> From: Jerry LeVan [mailto:jlevan@adelphia.net]
> Sent: Monday, May 31, 2004 8:18 PM
> To: Postgres General
> Subject: [GENERAL] Running Totals and stuff...
>
>
> 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.

Eventually, the OID values will wrap-around.  You cannot safely assume
that they are ordered.

> 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?

Why not just use a "group by" that involves all the things you want to
group by?  Probably, I do not properly understand what you are trying to
do.

If you create a 64 bit bigint sequence called "CheckSequence" it is
pretty unlikely that it will wrap around from normal usage.