Running total/ Cumulative Sum using analytical function

Let’s say you want to calculate running sum or cumulative total of Salary for a given company starting from Jan 2010 to current month. Let’s assume that you have a table (Employees) in your database that contains all information needed to calculate running total.

For example purpose we will be using SQL Analytical function to calculate Running total. The syntax for calculating running total can be as follows;

select Hire_Date ,last_name ,Salary, sum(Salary) OVER (Partition by '' order by hire_date asc rows unbounded preceding ) RunningTotal
from employees; 

As you can see in the above example that we are using sum () OVER (Partition by order by ROWS unbounded preceding) clause to calculate running total. It is basically doing sum of salary and keep adding them until the last record found in the table.



The PARTITION BY clause defines the group whose rows will be added by the SUM Window Function.

Order by Clause defines how you want to order hire_date while doing running total on salary. Please note that result output will be different depending on sort order.

ROWs Unbounded preceding: - Rows unbounded preceding aggregates from the start of the group to the current row. As we already mentioned group are defined on partition by clause.



In the following example, we want to calculate running total for each group rather than doing a running total for entire division. Let’s assume that we have many departments in our company and want to calculate running total for each departments.




Here is an example:

Select Hire_date , last_name ,Salary, sum(Salary) OVER (Partition by department_id order by hire_date asc rows unbounded preceding ) runningTotal
from employees; 

As you can see in the example, the only place where we added department_id was on Partition_by clause. SQL will create groups for each departments and will do separate running total for each of them.

Database :- Teradata & Oracle