pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
cumulative sum in aggregate query. 2+ messages / 2 participants [nested] [flat]
* cumulative sum in aggregate query. @ 2001-07-11 05:30 David Stanaway <david@netventures.com.au> 0 siblings, 1 reply; 2+ messages in thread From: David Stanaway @ 2001-07-11 05:30 UTC (permalink / raw) To: pgsql-sql Hi there, 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? -- Best Regards David Stanaway ========================.--------------------------------------------- Technology Manager - Australia's Premier Internet Broadcasters david@NetVentures.com.au Office +612 9357 1699 ========================'--------------------------------------------- ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: cumulative sum in aggregate query. @ 2001-07-11 13:51 Tom Lane <tgl@sss.pgh.pa.us> parent: David Stanaway <david@netventures.com.au> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2001-07-11 13:51 UTC (permalink / raw) To: David Stanaway <david@netventures.com.au>; +Cc: pgsql-sql David Stanaway <david@netventures.com.au> 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2001-07-11 13:51 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2001-07-11 05:30 cumulative sum in aggregate query. David Stanaway <david@netventures.com.au> 2001-07-11 13:51 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox