Skip site navigation (1) Skip section navigation (2)

Re: running aggregates entirely in SQL?

From: Aditya <aditya(at)grot(dot)org>
To: sfpug(at)postgresql(dot)org
Subject: Re: running aggregates entirely in SQL?
Date: 2004-05-11 20:37:08
Message-ID: 20040511203708.GD71819@mighty.grot.org (view raw or flat)
Thread:
Lists: sfpug
On Tue, May 11, 2004 at 11:01:13AM -0700, Brian Ghidinelli wrote:
> Joe Celko has some examples in his "SQL for Smarties" book for doing 
> running aggregates entirely in SQL.  I believe they will work in most 
> database servers.  I don't have the book handy but it's a place to look.

Thanks for all the suggestions, and of course the most obvious way (in
hindsight) eluded me:

a table mytable with:

  id, tdate, description, cost

and you can produce a "invoice" like thing with a running total as:

select
 t.id,
 t.tdate,
 t.description,
 t.cost,
 (select sum(cost) from mytable where tdate <= t.tdate)
from
 mytable t
order by
 2
;

needless to say, the subselect gets more expensive as your table gets larger
so using an "aggregates" table is probably worth doing for large tables.

Adi
 

In response to

sfpug by date

Next:From: Brian GhidinelliDate: 2004-05-11 20:39:49
Subject: Re: running aggregates entirely in SQL?
Previous:From: Josh BerkusDate: 2004-05-11 19:35:30
Subject: Re: running aggregates entirely in SQL?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group