| From: | "Levan, Jerry" <Jerry(dot)Levan(at)EKU(dot)EDU> |
|---|---|
| To: | <pgsql-general(at)postgresql(dot)org> |
| Subject: | Running Totals and other stuff.... |
| Date: | 2004-06-01 12:40:19 |
| Message-ID: | 30ECD69B1163C64EA657B3B29D4A938A3B9DFB@FACSTAFF.facultystaff.eku.edu |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=windows-1252">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.0.6396.0">
<TITLE>Running Totals and other stuff....</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<BR>
<P><FONT SIZE=2>Humpfff...Last night I tried posting this and found that<BR>
dynamic IP's are now prevented from posting to the list...<BR>
<BR>
Did I miss the announcement?<BR>
<BR>
************************************<BR>
<BR>
Hi,<BR>
<BR>
I keep all of my financial data in Postgresql ( 7.4.2).<BR>
My "Check" register records deposits, withdrawals (as amount) , date,<BR>
category and other stuff.<BR>
<BR>
The following sorta works...<BR>
<BR>
SELECT oid, *, (SELECT sum(amount) FROM checks WHERE x.thedate >= thedate ) AS total<BR>
FROM checks x<BR>
ORDER BY thedate,oid ;<BR>
<BR>
The problem is that all transactions on the same date get the total of all<BR>
transactions for that date, so the resulting table is only "sorta" a<BR>
running total.<BR>
<BR>
If I change the rascal to look like<BR>
SELECT oid, *, (SELECT sum(amount) FROM checks WHERE x.oid >= oid ) AS total<BR>
FROM checks x<BR>
ORDER BY thedate,oid ;<BR>
<BR>
I get the right results, but this relies on the fact the oids in the<BR>
check table are currently *sorted* (when the table is sorted by thedate)<BR>
at least it appears that way via<BR>
a very brief inspection.... I suspect if I deleted a record and added<BR>
a record the oids would get out of sequence.<BR>
<BR>
Is there a slick way to tell if a column (say the oids column) is in "sorted"<BR>
order when the table is sorted by date?<BR>
<BR>
Assuming the oids get out of wack with respect to the date, is it possible<BR>
to easily construct a table of the checks sorted by date and then "glue on"<BR>
a column of ascending integers so the running total sql statement will<BR>
function properly?<BR>
<BR>
Jerry<BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 2.1 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Richard Huxton | 2004-06-01 12:41:20 | Re: after using pg_resetxlog, db lost |
| Previous Message | Mike Rylander | 2004-06-01 11:36:44 | Re: multiple count functions in a select statement |