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.
You may also be interested in reading these articles:
T-SQL – REPLICATE() Function: Formatting and Masking Techniques
T-SQL Trick: Use Recursive CTE To Get Hierarchical Data
T-SQL Trick: Use CTE to Render Dates In The Specified Date Range
Sql Tip: How to Find Duplicate Records In A Table
Concatenating Rows in Sql Query
