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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Brian Ghidinelli | 2004-05-11 20:39:49 | Re: running aggregates entirely in SQL? |
Previous Message | Josh Berkus | 2004-05-11 19:35:30 | Re: running aggregates entirely in SQL? |