Re: cumulative sum in aggregate query.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: David Stanaway <david(at)netventures(dot)com(dot)au>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: cumulative sum in aggregate query.
Date: 2001-07-11 13:51:22
Message-ID: 18076.994859482@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

David Stanaway <david(at)netventures(dot)com(dot)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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Peter Eisentraut 2001-07-11 14:47:43 Re: can we write to a flat file from Postgresql procedure
Previous Message Jan Wieck 2001-07-11 12:36:09 Re: can we write to a flat file from Postgresql procedure