How to get cumulative sum in SQL using analytical function?

In my previous articles I have provided different system users of oracle with passwords and also given heads-up about multiple analytical queries in SQL. In this article I would like to provide the straightforward query to get cumulative sum in SQL. There are so many times user needs to add the logic for cumulative sum using SQL. Many user finds difficulty and will write most complex sql query for the same. In this article i will try to give simple straightforward approach to get cumulative sum in SQL.

What is mean by cumulative sum in SQL ?

The cumulative sum is nothing but running total which will give the display of total sum of data which will raise in series or progression. Example – 10,20 = 30, 10,20,30=60.

There are many times where you require to find out the cumulative sum in SQL. We require to use the analytical function Sum with partition by to find out cumulative sum.

The above table will give you the exact essence of the cumulative sum. We can do this kind of calculation in oracle using Sum with order by clause. There are multiple ways to calculate cumulative sum in SQL.

Query 1 : Cumulative Sum in Oracle :

You can calculate cumulative sum in Oracle SQL using straightforward function named Sum and order by together. If you want partitioned data then you can use partition by clause for the same.

Real time scenario :

Lets say you require to calculate cumulative sum for the employees in the department. how do you calculate that? Consider table name as Employee.

Query :

select
dept_no Department_no,
count(empno) Employee_Per_Dept,
sum(count(*)) over (order by deptno) Cumulative_Total
from Employee
group by deptno;

Output :

Cumulative Sum in SQL Server :

In SQL server also you can calculate cumulative sum by using sum function. We can use same table as sample table.

select dept_no Department_no, count(empno) Employee_Per_Dept, sum(count(*)) over (order by deptno) Cumulative_Total from [DBO].[Employee] group by deptno;

The output will be same as displayed above.

Cumulative Sum Using Self Join :

You can calculate cumulative sum using self join also. Lets consider the table name is sales and you need to calculate cumulative sum according to region.

Select S1.ID,S1.Region,S1.Product,S1.QTR,S1.NetSales,
           Sum(S2.NetSales)Cumulative_Net
       From  T_Sales1 S1,
                 T_Sales2 S2
       Where S2.ID<= S1.ID
Group By S1.ID,S1.Region,S1.Product,S1.QTR,S1.NetSales
Order BY S1.ID;

Cumulative Sum using Correlated Subquery :

You can also calculate the cumulative sum in SQL using correlated subquery. If you want to calculate cumulative sales for the specific quater then you can use following query.

  Select * , ( Select Sum(NetSales) From t_SALES S2
                        Where S2.SALES_ID<= S1.SALES_ID
                      )  Cumulative_SALE
      From t_SALES S1
      Order BY S1.SALES_ID;

The above query will give the output as following:

These are the different ways where we can calculate the cumulative sum in SQL. Hope you get clear idea about calculating the cumulative sum in SQL. If you have any issues or concerns with the same kindly comment in comments section.

Are you struggling with coding? Don’t worry, you’re not alone! As coding assignments can be complex and time-consuming, many students look for SQL assignment help to get their work done.

If you’re looking for professional assistance with coding your SQL assignment, our team of experts is here to help. Our experienced developers have been coding for years and can provide the necessary guidance and advice to get your coding assignment done perfectly. With their help, you can rest assured that your SQL coding will meet all the requirements of your assignment. Plus, our team is available 24/7 to answer any questions you may have along the way.