SQL Server - How to create an AutoSum (same as Excel) using Window functions (Running totals)

Views: 1.875
Reading Time: 4 minutes

Hello people,
How are you ?

Today I am going to introduce an interesting feature of SQL Server that most people do not know about, which is the use of Window functions to perform cumulative calculations on a SQL Server result set, similar to Excel's Auto Sum feature.

This need arose for me through a request similar to the one I will submit in this post, where we have a dated sales base and want to select the months where the year's cumulative sales amount was less than 40. If this value is reached during the month, this month should not be selected.

To come up with this information, I thought of grouping the data by month and creating an cumulative sum of sales quantity. With a small data mass, we can create a WHILE loop to traverse all rows in the table, retrieve the amount of that row, add a variable, and update this record again to do the sum, but when we apply it to a table with millions of records, This process may take hours to process.

If you want to know a few more different ways to group data, visit the post.SQL Server - Grouping Data Using ROLLUP, CUBE, and GROUPING SETS.

Test Base

Use this script to assemble the test base for this post.

Populated Sales Table Example:

Implementing the AutoSum Feature in SQL Server

To implement the AutoSum feature in SQL Server, we will use the SUM () aggregate function in conjunction with a Window function, as well as some parameters specially designed for this need.

If you want to implement this using the WHILE solution I mentioned above, you can do it as shown below. I needed to do something similar on a table of 1.4 million records and by my calculations, it was going to take a few hours just to process it, which eventually made me look for a new solution.

Example of Populated Grouped Sales Table:

To do this much more quickly and conveniently, let's use an aggregate function and Window function. In the example I gave above, in a table of 1.4 million records (already grouped), this query took 8 seconds to process.

Result:

That's it, guys.
I hope you enjoyed this post.

Hug!

SQL Server - How to create an autosum (Excel-like) autosum like excel excel-like using Window functions running totals running totals

SQL Server - How to create an autosum (Excel-like) autosum like excel excel-like using Window functions running totals running totals