Thread: 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.

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

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

What platforms now support 64 bit ints for sequences?


"Dann Corbit" <DCorbit@connx.com> wrote:

<quote ------------------------------------------------>
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.

<quote ------------------------------------------------>

Dennis Gearon <gearond@fireserve.net> writes:
> What platforms now support 64 bit ints for sequences?

I think all of the currently-marked-as-supported ones do.

(Hint: if your platform passes the sequence regression test,
it has 64-bit sequences.)

            regards, tom lane