pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Stanaway <david@netventures.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: cumulative sum in aggregate query.
Date: Wed, 11 Jul 2001 09:51:22 -0400
Message-ID: <18076.994859482@sss.pgh.pa.us> (raw)
In-Reply-To: <E15KD3X-0004lz-00@runt.in.netventures.com.au>
References: <E15KD3X-0004lz-00@runt.in.netventures.com.au>

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



view thread (2+ messages)

Message-ID: <18076.994859482@sss.pgh.pa.us>
Permalink:  ../18076.994859482@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/18076.994859482@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, david@netventures.com.au
  Subject: Re: cumulative sum in aggregate query.
  In-Reply-To: <18076.994859482@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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