Running Total in PL/SQL
Problem: You need to display a result with a running total in PL/SQL query.
The code below will accomplish displaying a running total in PL/SQL:
SELECT C.VENDOR_ID, C.VENDOR_NAME_SHORT, A.BUSINESS_UNIT, A.VOUCHER_ID,
A.INVOICE_ID, A.INVOICE_DT, A.GROSS_AMT,
SUM(A.GROSS_AMT) OVER (ORDER BY C.VENDOR_ID, A.BUSINESS_UNIT) "RUNNING TOTAL"
FROM PS_VOUCHER A, PS_VENDOR C
WHERE (A.PROCESS_MAN_CLOSE = 'N' OR A.CLOSE_STATUS <> 'C')
AND A.ENTRY_STATUS <> 'X'
AND A.VENDOR_SETID = C.SETID
AND A.VENDOR_ID = C.VENDOR_ID
AND C.VENDOR_ID = '0000000001'
GROUP BY C.VENDOR_ID, C.VENDOR_NAME_SHORT, A.BUSINESS_UNIT, A.VOUCHER_ID,
A.INVOICE_ID, A.INVOICE_DT, A.GROSS_AMT
ORDER BY A.GROSS_AMT, C.VENDOR_ID, A.BUSINESS_UNIT, A.VOUCHER_ID
This is a pretty simple query to show the details of a vendor balance. The column to be used in calculating the “running total” needs to be listed twice. First without the
SUM() and once with the SUM() in the SELECT statement. The SUM(A.GROSS_AMT) is followed by the OVER command to create the running total. The columns listed in the ORDER BY clause following the the OVER command provide the logic to indicate when to change the sum amount. Whenever the value in any of the ORDER BY parameters changes then the “running total” result is updated summing up all the rows having the current C.VENDOR_ID, A.BUSINESS_UNIT .
A more simple example
SELECT BUSINESS_UNIT, VOUCHER_ID, GROSS_AMT, SUM(GROSS_AMT) OVER (ORDER BY BUSINESS_UNIT) "RUNNING TOTAL"
GROUP BY BUSINESS_UNIT, VOUCHER_ID, GROSS_AMT
ORDER BY BUSINESS_UNIT
In this simpler example “running total” only increments when BUSINESS_UNIT is encountered. Even though the VOUCHER_ID is changing constantly the “running total” result only cares when the BUSINESS_UNIT is changed. These two examples of a running total in PL/SQL as a result column.