# Easiest Way of Calculating Running Total in SQL Server with SUM() Window Function

In this post, we'll go over examples of calculating running total using **SUM()** Window Function.

Using Window Function is the easiest and fastest way to calculate running total in SQL Server as it is built-in and designed for this analytical purpose.

You can use this DB Fiddle to follow along and calculate Running Total with SUM() Window Function.

## Sample Data for Running Total Calculation

Let’s create some sample data to demonstrate how to calculate running total using SUM() Window Function.

We will create a `EmployeeSales`

table which has a `EmployeeID`

, `SaleDate`

, and `SaleAmount`

columns. `SaleAmount`

stores each employee's daily sales amount.

`CREATE TABLE EmployeeSales(`

[EmployeeID] INT,

[SaleDate] DATE,

[SaleAmount] INT

)

INSERT INTO EmployeeSales VALUES

(1,'2022-06-01',10),

(1,'2022-06-02',20),

(1,'2022-06-03',30),

(2,'2022-06-01',10),

(2,'2022-06-02',10),

(2,'2022-06-03',10)

EmployeeID | SaleDate | SaleAmount |
---|---|---|

1 | 2022-06-01 | 10 |

1 | 2022-06-02 | 20 |

1 | 2022-06-03 | 30 |

2 | 2022-06-01 | 10 |

2 | 2022-06-02 | 10 |

2 | 2022-06-03 | 10 |

## Calculate Running Total with SUM() Window Function

The window function SUM() OVER makes generating a running total a very easy task.

We start by partitioning data by `EmployeeID`

and order data within each partition by `SaleDate`

.

Then `SUM(ES.SaleAmount)`

aggregate function gets applied for **ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW** to compute running total for each row.

`SELECT *`

,SUM(ES.SaleAmount) OVER (

PARTITION BY ES.EmployeeID ORDER BY ES.SaleDate

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW

) AS RunningTotal

FROM EmployeeSales AS ES

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the default behaviour for SUM() so we could omit this clause and still get the same results.

EmployeeID | SaleDate | SaleAmount | RunningTotal |
---|---|---|---|

1 | 2022-06-01 | 10 | 10 |

1 | 2022-06-02 | 20 | 30 |

1 | 2022-06-03 | 30 | 60 |

2 | 2022-06-01 | 10 | 10 |

2 | 2022-06-02 | 10 | 20 |

2 | 2022-06-03 | 10 | 30 |

Let's try to calculate running total of entire dataset starting with the earliest `SaleDate`

.

`SELECT *`

,SUM(ES.SaleAmount) OVER (

ORDER BY ES.SaleDate

) AS RunningTotal

FROM EmployeeSales AS ES

Note that we omit PARTITION BY clause in this case as we're calculating running total for the entire dataset.

You will notice that row 1 (should be 10), row 3 (should be 30), and row 5 (should be 80) have incorrect `RunningTotal`

value.

Why is this happening?

This is because we only specify `SaleDate`

in the ORDER BY clause which results in duplicate values.

For example, rows 1 and 2 have the same `SaleDate`

**2022-06-01**. Rows 3 and 4 have the same `SaleDate`

**2022-06-02**

EmployeeID | SaleDate | SaleAmount | RunningTotal |
---|---|---|---|

1 | 2022-06-01 | 10 | 20 |

2 | 2022-06-01 | 10 | 20 |

2 | 2022-06-02 | 10 | 50 |

1 | 2022-06-02 | 20 | 50 |

1 | 2022-06-03 | 30 | 90 |

2 | 2022-06-03 | 10 | 90 |

We can fix this by providing a combination of columns to ORDER BY clause which can generate unique combined value for each row.

We know that any combination of `EmployeeID`

and `SaleDate`

is unique so we can specify these 2 columns in the ORDER BY clause.

We now have the correct progression of the running total value.

`SELECT *`

,SUM(ES.SaleAmount) OVER (

ORDER BY ES.SaleDate

,ES.EmployeeID

) AS RunningTotal

FROM EmployeeSales AS ES

EmployeeID | SaleDate | SaleAmount | RunningTotal |
---|---|---|---|

1 | 2022-06-01 | 10 | 10 |

2 | 2022-06-01 | 10 | 20 |

1 | 2022-06-02 | 20 | 40 |

2 | 2022-06-02 | 10 | 50 |

1 | 2022-06-03 | 30 | 80 |

2 | 2022-06-03 | 10 | 90 |

Window Function is only available in SQL Server 2012 or later. If you're running an older version of SQL Server, I recommend using self join technique to calculate running total.