pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / 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>
  2001-07-11 13:51 ` Re: cumulative sum in aggregate query. Tom Lane <tgl@sss.pgh.pa.us>
  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 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>
  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