Calculate Running Total in SQL

In this article we will show you, How to write a SQL Query to Calculate Running Total in SQL Server with example. This is one of the common SQL Interview Question that you might face in the interviews. For this example, We are going to use the below shown data

Above screenshot will show you the data inside the NewCustomer table present in the SQL Tutorial database.

Calculate SQL Running Total Example 1

In this example we will show you, How to Calculate SQL Server Running Total using the SUBQUERY.

-- Query to Calculate Running Total in SQL Server
USE [SQL Tutorial]
GO
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

OUTPUT

Calculate Running Total in SQL Server Example 2

In this example we will show you, How to calculate SQL Server Running Total using the JOIN, GROUP BY, and ORDER BY Clause.

-- Query to Calculate Running Total in SQL Server
USE [SQL Tutorial]
GO
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]

OUTPUT

Calculate Running Total in SQL Server Example 3

In this example we will show you, How to calculate SQL Server Running Total using the SUM Function, and OVER.

-- Query to Calculate Running Total in SQL Server
USE [SQL Tutorial]
GO
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,SUM([YearlyIncome]) OVER (
			          ORDER BY  [CustID]
				) AS [Running Total]
  FROM [NewCustomers]

and the more traditional way is

-- Query to Calculate SQL Server Running Total
USE [SQL Tutorial]
GO
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,SUM([YearlyIncome]) OVER (
			          ORDER BY  [CustID] ROWS UNBOUNDED PRECEDING
				) AS [Running Total]
  FROM [NewCustomers]

OUTPUT

Thank You for Visiting Our Blog