Re: SQLPlus: How to do Running Totals?

derwin_at_mach2.wlu.ca (Daryl Erwin) writes:

> This seems like a trivial request, but I cant figure it out...
> I want....
>
> FieldA RunTot <--- Computed Field
> ------ ------
> 1 1
> 1 2
> 2 4
> 2 6

If your results are sorted on FieldA (either ascending or descending), and the values in FieldA are unique (which admittedly is not the case in your example - I'm not sure if that's the crux of your question), then the following will work:

SELECT	T1.FIELDA, SUM(T2.FIELDA) RUNTOT
FROM	MYTABLE T1, MYTABLE T2
WHERE	T2.FIELDA <= T1.FIELDA	/* Should be >= if ORDER BY is DESCending */
GROUP	BY T1.FIELDA
ORDER	BY T1.FIELDA ;

I hope someone posts a more general solution since I'd love to see it!

David Hazledine                                                EMBL Data Library
Database Administrator                                                PF 10.2209
EMBL Data Library                                       6900 Heidelberg, Germany

Internet: Hazledine_at_EMBL-Heidelberg.DE