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
