Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6BDpga72756 for ; Wed, 11 Jul 2001 09:51:42 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f6BDpMJ18079; Wed, 11 Jul 2001 09:51:23 -0400 (EDT) To: David Stanaway cc: pgsql-sql@postgresql.org Subject: Re: cumulative sum in aggregate query. In-reply-to: References: Comments: In-reply-to David Stanaway message dated "Wed, 11 Jul 2001 15:30:29 +1000" Date: Wed, 11 Jul 2001 09:51:22 -0400 Message-ID: <18076.994859482@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200107/142 X-Sequence-Number: 3934 David Stanaway writes: > I have a query that gives me x/y data for a graph, and at the moment, > the y data is relative. > EG: > x | y > 1.2 | +1 > 1.9 | 0 > 3.4 | +4 > 5.2 | -2 > 6.7 | -1 > 9.3 | 0 > 11.3 | -1 > Now, I want to convert this result into a result like this: > x | y > 1.2 | 1 > 1.9 | 1 > 3.4 | 5 > 5.2 | 3 > 6.7 | 2 > 9.3 | 0 > 11.3 | 1 > Does anyone have any suggestions as to how to do this? AFAIK the only way to do that in SQL is like so: SELECT x, (SELECT sum(y) FROM tab innertab WHERE innertab.x <= outertab.x) FROM tab outertab ORDER BY x; This will, of course, be horrendously inefficient for large numbers of rows, but given that SQL doesn't believe in ordered data inside a computation, I can't see any other way to do it in pure SQL. If you've got lots of data, you should consider just doing "SELECT x,y FROM tab ORDER BY x" and then forming the running sum on the application side. BTW, I didn't come up with that on the spur of the moment --- I got it from Joe Celko's "SQL For Smarties". Highly recommended book. regards, tom lane