Categories: SQL Complex Queries

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.

Cumulative Sum Example ( Source : Hana Blog)

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 :

Department_noEmployee_Per_DeptCumulative_Total
1011212
1021022
1031133
Output : Cumulative Sum

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:

IDProductQTRNET SALESCUMULATIVE SUM
1Computer11010
2Computer22030
3Computer33060
Cumulative Total

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.

Amit S

Oracle Consultant with vast experience in Oracle BI and PL/SQL Development. Amiet is the admin head of this website who contributes by preparing tutorials and articles related to database technologies. He is responsible to manage the content and front-end of the website.

Share
Published by
Amit S

Recent Posts

The Essential Guide to Cryptocurrency Exchange Platform Development for Beginners and Beyond

Introduction Cryptocurrencies took the world by storm, setting up a new financial system and breaking…

5 months ago

Top 20 System Administrator Interview Questions and answers

In my previous article I have given Top 20 technical support interview questions with its…

8 months ago

Desktop Support Scenario Based Interview Questions

In my previous articles I have given 15 most asked desktop support interview questions with…

8 months ago

How Do Business Analysts Use SQL for Their Needs?

A business analyst is someone who is versed in processes of data analysis used for…

8 months ago

Top 15 Control-M Interview Questions with Answers

In my previous article I have already given top questions and answers for Desktop support…

8 months ago

Top 20 SQL Interview Questions for Production Support

In my previous article I have given unix production support interview questions and answers. In…

8 months ago