Running Total in PL/SQL

by Andy
Posted on February 14, 2018March 13, 2018

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

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"
FROM PS_VOUCHER
GROUP BY BUSINESS_UNIT, VOUCHER_ID, GROSS_AMT
ORDER BY BUSINESS_UNIT

SELECT BUSINESS_UNIT, VOUCHER_ID, GROSS_AMT, SUM(GROSS_AMT) OVER (ORDER BY BUSINESS_UNIT) "RUNNING TOTAL"

FROM PS_VOUCHER

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.