How to calculate running totals in SQL Server

Posted on Nov 23 2013 - 6:24pm by admin

Let us consider a database is capturing its company’s sales information in a table Customer_Sales. It holds the information like SalesID, CustomerID, SaleDate and Amount.

Let us create some sample data to understand the data.

CREATE TABLE Customer_Sales
(
SalesID INT NOT NULL IDENTITY(1,1),
CustomerID INT NOT NULL,
SaleDate DATE NOT NULL,
Amount INT NOT NULL
)

INSERT INTO Customer_Sales(CustomerID, SaleDate, Amount) VALUES(1, '2013/01/01', 100)
INSERT INTO Customer_Sales(CustomerID, SaleDate, Amount) VALUES(2, '2013/01/01', 25)
INSERT INTO Customer_Sales(CustomerID, SaleDate, Amount) VALUES(3, '2013/01/01', 10)
INSERT INTO Customer_Sales(CustomerID, SaleDate, Amount) VALUES(1, '2013/01/05', 22)
INSERT INTO Customer_Sales(CustomerID, SaleDate, Amount) VALUES(2, '2013/01/05', 65)
INSERT INTO Customer_Sales(CustomerID, SaleDate, Amount) VALUES(4, '2013/01/05', 70)
INSERT INTO Customer_Sales(CustomerID, SaleDate, Amount) VALUES(3, '2013/01/07', 19)
INSERT INTO Customer_Sales(CustomerID, SaleDate, Amount) VALUES(5, '2013/01/07', 28)
INSERT INTO Customer_Sales(CustomerID, SaleDate, Amount) VALUES(5, '2013/01/08', 10)
INSERT INTO Customer_Sales(CustomerID, SaleDate, Amount) VALUES(1, '2013/01/09', 260)
INSERT INTO Customer_Sales(CustomerID, SaleDate, Amount) VALUES(5, '2013/01/09', 30)

Running Total for the whole data:

SELECT s1.SalesID, s1.CustomerID, s1.SaleDate,
		(SELECT SUM(s2.Amount)
		 FROMFROM Customer_Sales s2
		 WHERE s2.SalesID <= s1.SalesID) AS Amount
FROM Customer_Sales s1

In the output, Amount is summed up to every other value for all the Sales. Result set will look like in the picture below.

total_1

Running Total for each Customer:

SELECT s1.SalesID, s1.CustomerID, s1.SaleDate,
		(SELECT SUM(s2.Amount)
		 FROM Customer_Sales s2
		 WHERE s2.CustomerID = s1.CustomerID
		 AND s2.SalesID <= s1.SalesID) AS Amount
FROM Customer_Sales s1

In the output, Amount is summed for each customer. Output will look like below total_2

*** Like Us on Facebook to get instant notifications about new posts ***
*** Click on the images to make them bigger in size to view good ***
*** Please leave your feedback below in the comments section if you have found this post useful ***

Leave A Response