running total in pl/sql

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:

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

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.