Re: running total (SQL query)

From: David Bath <dtb_at_otto>
Date: 7 Feb 93 13:51:06 GMT
Message-ID: <dtb.729093066_at_otto>

rbroders_at_oracle.com (Robert Brodersen) writes:

>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

IMHO, while the SQL is fine, if the table gets big this sort of thing can run like a dog.

You might like to consider creating a temp table and running through the source data with PL/SQL keeping an accumulator column and variable and then a single scan through the source table and then the temp table is all you need.

David T. Bath             | Email:dtb_at_otto.bf.rmit.oz.au (131.170.40.10)
Senior Tech Consultant    | Phone: +61 3 347-7511 TZ=AEST-10AEDST-11
Global Technology Group   | 179 Grattan St, Carlton, Vic, 3153, AUSTRALIA

"The robber of your free will does not exist" - Epictetus

-- 
David T. Bath             | Email:dtb_at_otto.bf.rmit.oz.au (131.170.40.10)
Senior Tech Consultant    | Phone: +61 3 347-7511 TZ=AEST-10AEDST-11
Global Technology Group   | 179 Grattan St, Carlton, Vic, 3153, AUSTRALIA
"The robber of your free will does not exist" - Epictetus

Received on Sun Feb 07 1993 - 14:51:06 CET