Re: running total (SQL query)

In article <C0r7uI.po_at_bc3.GUN.de> bruno_at_bc3.GUN.de (Bruno Cirone) writes:
> I have this table:
>
> Name Score
>
> Werner 200
> Hugo -100
> Heinz 300
> Bruno 100
>
> I want this table to be printed like the following:
>
> Name Score total
>
> Werner 200 200
> Hugo -100 100
> Heinz 300 400
> Bruno 100 500
>
> How can I do that with Oracle (without report-generator) ?

This can be accomplished quite easily in SQL with a self-join. The technique requires that you order your result. Your rows are in descending Name order, so I assume that is what you intended:

select a.name, a.score, sum(b.score)
  from scores a, scores b
 where a.name <= b.name
group by a.name, a.score
order by a.name desc

Basically, the query builds a cartesian product of all rows in the table joined to itself. It then eliminates all rows which shouldn't be included in the sum (where a.name <= b.name - note, if you change the ordering, you must reverse this clause). Then, we group by all selected columns in a to compress the sum, and order the result. Simple eh?

--
Thanks-           rbroders_at_oracle.com       -Bob Brodersen (415)-506-2189
Applications Architect, Applications Technology Group, Applications Division
                  Oracle Corporation, Redwood Shores, CA 94065