How to get sum of cursor results
410544Dec 29 2003 — edited Dec 31 2003I have a recurring problem where I iterate over a cursor and then want to know the sum of results from that cursor. The three obvious solutions are a) have local PLSQL variables which are increased every iteration through the cursor, or b) a final select which does the summation independently of the cursor, or c) add colums to the cursor which are the sums. I would rather have Oracle do the sum-ing (so a) is out), and it would be nice to only have a single select call to the database (so b) is out), but I really don't like c) because it usually doubles the size of the cursor row (ie. each row contains the original columns plus the sum of each column).
Does anyone know of a nifty way to get Oracle to add a final row to those returned by the cursor which is the summation of all the results (for columns of interest?)
The obvious application is for things like totals for billing or sales invoices or employee salary or whatever.
Thanks,
James