Calculate Running Total in SQL

How to write a query to Calculate Running Total in SQL Server with example. For this frequently asked interview question, we will use the below-shown data.

Customer Table data 1

Calculate SQL Server Running Total Example

In this example, we will show you how to find the Running Total using the SUBQUERY.

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,(
	 SELECT SUM(CUST2.[YearlyIncome]) 
         FROM [NewCustomers] AS CUST2
         WHERE CUST2.[CustID] <= CUST1.[CustID]
	) AS [Running Total]
  FROM [NewCustomers] AS CUST1
Calculate Running Total in SQL Server 2

This SQL Server example shows how to calculate Running Total using the JOINGROUP BY, and ORDER BY Clause.

SELECT CUST1.[CustID]
      ,CUST1.[FirstName]
      ,CUST1.[LastName]
      ,CUST1.[Education]
      ,CUST1.[Occupation]
      ,CUST1.[YearlyIncome]
      ,SUM(CUST2.[YearlyIncome]) AS [Running Total]
FROM [NewCustomers] AS CUST1,
     [NewCustomers] AS CUST2	   
WHERE CUST2.[CustID] <= CUST1.[CustID]
GROUP BY CUST1.[CustID]
	,CUST1.[FirstName]
        ,CUST1.[LastName]
        ,CUST1.[Education]
        ,CUST1.[Occupation]
        ,CUST1.[YearlyIncome]
ORDER BY CUST1.[CustID]
SQL Calculate Running Total using the JOIN, GROUP BY, and ORDER BY Clause 3

In this SQL Server example, we will find the Running Total using the SUM Function and OVER.

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,SUM([YearlyIncome]) OVER (
			          ORDER BY  [CustID]
				) AS [Running Total]
  FROM [NewCustomers]

and the more traditional way is

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,SUM([YearlyIncome]) OVER (
			          ORDER BY  [CustID] ROWS UNBOUNDED PRECEDING
				) AS [Running Total]
  FROM [NewCustomers]
Calculate Running Total in SQL using SUM and Order By 4

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.

Comments are closed.