Introduction This very frequent needful thing for developers as they need to create so many reports based on this concept.
For this you will have to use one of the window functions in oracle which is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
This would add each and every value with previous value and give like Running Total.
Query for this, {codecitation class="brush: sql; gutter: true;" width="650px"}
SELECT PRODUCT_NO,PL_NO, UNRESTRICTED_QTY,
SUM(UNRESTRICTED_QTY) OVER (ORDER BY PRODUCT_NO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RUNNING_TOTAL
FROM PRODUCT_LOCATION
WHERE CLIENT_C=UPPER('MSWG') AND UNRESTRICTED_QTY>0 AND LOCATION_NO='RECEIPT_BAY'
{/codecitation}
 Explanation
In this query, UNBOUNDED PRECEDING means start of the window is fixed at first row in the result set and adding up the values with currently reading value which was specified by CURRENT ROW up to last record of the record set.
And ordering results by PRODUCT_NO
The result of the above query has shown as below.
Result
I hope you ae like it and enjoy with happy PL/SQL Thank you Murukan |