SQL Tips Calculate Running Totals.pptx
Download as PPTX, PDF0 likes6 views
The document explains how to calculate running totals in SQL Server Management Studio (SSMS) using a window function. It provides a sample SQL query that sums the sales gross column ordered by sale dates to generate a running total. The content also mentions the flexibility of this method to derive other calculations like running counts or averages.
![Assuming you have a table of sales records
Select SaleDate, Sales_Gross
from [dbo].[SalesSample]
But you need to see the running total of sales at
the end of each date](https://image.slidesharecdn.com/sqltipscalculaterunningtotals-220422081646/95/SQL-Tips-Calculate-Running-Totals-pptx-4-638.jpg)
![Using a ‘window function’ we can calculate the
running total from the Sales (Gross) column
Select SaleDate, Sales_Gross
, sum(Sales_Gross) over (order by SaleDate asc) as [Running Total]
from [dbo].[SalesSample]](https://image.slidesharecdn.com/sqltipscalculaterunningtotals-220422081646/95/SQL-Tips-Calculate-Running-Totals-pptx-5-638.jpg)
![Breaking this down
Select SaleDate, Sales_Gross
, sum(Sales_Gross) over (order by SaleDate asc) as [Running Total]
from [dbo].[SalesSample]
sum(Sales_Gross)
Defines which method of aggregation to use on which column
over (order by SaleDate asc)
Provides the details of how you want the running total to ‘sort’
as [Running Total]
Defines the name of the new column](https://image.slidesharecdn.com/sqltipscalculaterunningtotals-220422081646/95/SQL-Tips-Calculate-Running-Totals-pptx-6-638.jpg)
SQL Tips Calculate Running Totals.pptx
- 2. How to Calculate Running Totals in SSMS
- 3. One of the most used reasons you might need to create a running total is to show a balance as at the date of the transaction
- 4. Assuming you have a table of sales records Select SaleDate, Sales_Gross from [dbo].[SalesSample] But you need to see the running total of sales at the end of each date
- 5. Using a ‘window function’ we can calculate the running total from the Sales (Gross) column Select SaleDate, Sales_Gross , sum(Sales_Gross) over (order by SaleDate asc) as [Running Total] from [dbo].[SalesSample]
- 6. Breaking this down Select SaleDate, Sales_Gross , sum(Sales_Gross) over (order by SaleDate asc) as [Running Total] from [dbo].[SalesSample] sum(Sales_Gross) Defines which method of aggregation to use on which column over (order by SaleDate asc) Provides the details of how you want the running total to ‘sort’ as [Running Total] Defines the name of the new column
- 7. You can easily amend this to create running counts, averages or other calculations
- 8. Hope you found this useful
- 9. For more Tips, Tricks and Timesavers, visit our website Tips and Timesavers | Select Distinct Limited Credit: [email protected]