Analytical SQL examples – Running total

AnalyticsPosted on Srdjan Stojadinovic

Running total

Running total is a very handy and useful tool.

Definition

From Wikipedia: “Running total is the summation of a sequence of numbers which is updated each time a number is added to the sequence, by adding the value of the new number to the previous running total.”

The main purposes of a running total are to save the state of summation trough a sequence of numbers without having to sum the entire sequence each time. 

Running Totals is used to keep track of:

  • Cumulative Balance (Year-To-Date)
  • Expenses and revenue
  • Employee hours
  • Inventory stocks

For calculating Running Totals, we would use Built-in SQL Server analytical functions (Window functions with OVER clause).

Examples are querying data from AdventureWorksDW2017 database and FactInternetSales. We are having three different types of measurements.

  • SUM/COUNT pr. day
  • SUM/COUNT pr. month (using function EOM)
  • Running Totals SUM/COUNT

We are taking data and partitioning OVER OrderDate to get Daily Sales.

For Running (Cumulative) Total we are summing over OrderDate and adding each sequence on the last Total. I have commented on each measure in the code below.
(Just click once in the box, mark code and copy to SQL Management Studio)

with CTE as(
SELECT distinct
-- Date used to partition you data
cast([OrderDate] as date) as OrderDate, 
-- Summing daily sales
sum(SalesAmount) OVER (PARTITION by OrderDate ) as DailySales,
 -- Adding daily sales for purpose of saving sequence of summation. 
sum(SalesAmount) OVER (ORDER by OrderDate) as CumulativeSales,


    --*** This measure is useful if you would like to show Cumulative Sales pr. Month. in report without making any logic in report *** 
case when OrderDate=EOMONTH(OrderDate) THEN sum(SalesAmount) OVER (ORDER by OrderDate) else 0 end as CumulativeSalesByMonth,
    --*********************************************************************************************************************************

sum(SalesAmount) OVER (PARTITION by eomonth(OrderDate))  as SalesByMonth, -- Montly sales
count([CustomerKey]) OVER (PARTITION by OrderDate) as NoOfDailyCustomers, -- Daily number of customers
count([CustomerKey]) OVER (PARTITION by eomonth(OrderDate)) as NoOfMonthlyCustomers, -- Number of customers by month


--*** This measure is useful if you would like to show Cumulative Nomber of Customers pr. Month in report without making any logic in report *** 

case when OrderDate=EOMONTH(OrderDate) THEN  count([CustomerKey]) OVER (ORDER by OrderDate) ELSE 0 END as CumulativeNoOfMonthlyCustomers,
--**********************************************************************************************************************************************
-- Adding daily Nomber of Customers for purpose of saving sequence of summation.
count(CustomerKey) OVER (ORDER by OrderDate) as CumulativeNoOfCustomers 
     
FROM [AdventureWorksDW2017].[dbo].[FactInternetSales]
)
--************** Wrapping everything up **************************
SELECT 
OrderDate,
DailySales,
CumulativeSales,
CumulativeSalesByMonth,
case when CumulativeSalesByMonth=0 then 0 else SalesByMonth end SalesByMonth,
NoOfDailyCustomers,
CumulativeNoOfCustomers,
case when CumulativeNoOfMonthlyCustomers=0 then 0 else NoOfMonthlyCustomers end NoOfMonthlyCustomers,
CumulativeNoOfMonthlyCustomers
FROM CTE
order by OrderDate

For making this short article with examples I have used low spec. MacBook Air 11,6″ mid-2012 with 4GB RAM running MacOS Mojave. I just wanted to test if it is possible to run Docker container with SQL Server 2017 on it. I have experienced no issues and getting query result back to Azure Data Studio was instantaneous.

Related Articles