Re: running aggregates entirely in SQL?

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