Computing a Simple Running Total of Salary Column in PL/SQL
In this example, we will calculate a simple running total of salary column of ‘w3wiki’ table. We will calculate and store the cumulative total by iterating through each row of our table ‘w3wiki’. Lets take a look into the query for more clear understanding.
Query:
DECLARE
v_runningTotal NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('ID | Name | Department | Salary | Running_Total');
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');
FOR i IN (SELECT * FROM w3wiki ORDER BY id) LOOP
v_runningTotal := v_runningTotal + i.salary;
DBMS_OUTPUT.PUT_LINE(i.id || ' | ' || i.name || ' | ' || i.department || ' | ' || i.salary || ' | ' || v_runningTotal);
END LOOP;
END;
Output:
Explanation : In the above query, we have iterated through each row of our table. We are also iterating in a specific order, i.e. order by ID column. Iterating through each rows, we are maintaining a cumulative total as storing in a variable. Then we will display our table’s data along with their respective cumulative sum. We can refer to the image for clear understanding of the working of our quer.
NOTE: Please do not get confused with the output lines used in the query. We have used them to make the output more readable and easy to understand.
How to Compute a Running Total in PL/SQL
Running total is a cumulative sum of a given data within a specific order. There are numerous use cases for calculating the running total. Calculating the running total helps in making predictions with the data and analyzing recent trends in data. This sometimes also helps in making a visual representation of data.
In this article, we will learn about how to compute a running total in PL/SQL where we can compute a running total with brief examples along with their respective explanations.