T-SQL Technique: Get Running Total Right From The Query

 

There are occasions when it is required to reveal running totals before rendering the grand total in a report. Usually calculating and providing running total is the job of a report designer software module. But if you’re using SQL Server database then Transact-SQL (a.k.a T-SQL) provides this facility right in the query.

Let’s assume that we have a “Sales” table with columns like “ProductID,” “SaleDate,” and “Amount.”, as shown in the image below.

 

 

We need to calculate the running total of sales for each product, ordered by date. The following little query will do the entire job for you.

 

SELECT
    s.ProductID,
    s.SaleDate,
    s.Amount,
    SUM(s.Amount) OVER (PARTITION BY s.ProductID ORDER BY s.SaleDate) AS RunningTotal

FROM
    Sales s

Running the provided query on this data, you might get a result like this:

 

This query helps you analyze how sales are accumulating over time for different products.