Computing Running Total Separately for Each Department in PL/SQL

In this example, we will calculate the running total of salary column but this time we will compute running total for different departments separately. Let us refer to the query block for more clear understanding.

Query:

DECLARE
    v_runningTotal NUMBER := 0;
    v_department VARCHAR2(50); 

BEGIN
    DBMS_OUTPUT.PUT_LINE('ID  | Name  | Department  | Salary   | Running_Total');
    FOR uniq_dept IN (SELECT DISTINCT department FROM w3wiki ORDER BY department DESC) LOOP
        v_department := uniq_dept.department;
        v_runningTotal := 0; 
        DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
        FOR i IN (SELECT * FROM w3wiki 
                    WHERE department = v_department ORDER BY id) LOOP
            v_runningTotal := v_runningTotal + i.salary;
            DBMS_OUTPUT.PUT_LINE(i.id || ' | ' || i.name || ' |   ' || v_department || '       | ' || i.salary || '    | ' || v_runningTotal);
        END LOOP;
    END LOOP;
END;

Output:

Explanation: In the above query, we first retrieve distinct department from our our table. We have extracted them in descending order of their department name. After extracting, we will run a for loop which will calculate the running total or cumulative total for each department separately. After that we will display an appropriate message. We can refer to the above shown image for more clear understanding of the working of our query.

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.

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.