How to calculate running total in SQL Server?

To calculate a running total in SQL Server, you can use the SUM() function in conjunction with the OVER() clause. This allows you to compute a cumulative sum across a specified order of rows. Here’s how you can do it:

Example Scenario

Let’s say you have a table named Sales with the following columns:

• SaleDate
• Amount

Sample Data

sqlCopy codeCREATE TABLE Sales (
    SaleDate DATE,
    Amount DECIMAL(10, 2)
);

INSERT INTO Sales (SaleDate, Amount) VALUES
('2024-01-01', 100.00),
('2024-01-02', 150.00),
('2024-01-03', 200.00),
('2024-01-04', 50.00);

SQL Query for Running Total

To calculate the running total of the Amount column ordered by SaleDate, you can use the following SQL query:

sqlCopy codeSELECT 
    SaleDate,
    Amount,
    SUM(Amount) OVER (ORDER BY SaleDate) AS RunningTotal
FROM 
    Sales
ORDER BY 
    SaleDate;

Explanation

• SUM(Amount) OVER (ORDER BY SaleDate): This computes the running total of the Amount column, ordered by SaleDate.
• AS RunningTotal: This gives a name to the calculated column.
• The ORDER BY SaleDate at the end of the query ensures the results are sorted by date.

Output

The output will look like this:

Additional Options

• Partitioning: If you want to calculate a running total for different groups (e.g., by category), you can use the PARTITION BY clause:

sqlCopy codeSELECT 
    SaleDate,
    Amount,
    SUM(Amount) OVER (PARTITION BY Category ORDER BY SaleDate) AS RunningTotal
FROM 
    Sales;

This approach helps manage more complex datasets where you might want to maintain running totals for different categories.

Feel free to ask if you have more questions or need further examples!