Tutorial Gateway

Calculate Running Total in SQL

by

How to write a SQL Query to Calculate Running Total in SQL Server with example. For this SQL Interview Question, We are going to use the below-shown data

Calculate Running Total in SQL Server 1

The 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 find 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 2

Calculate Running Total Example 2

This example shows how to calculate 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 3

Calculate Running Total Example 3

In this case, we show you how to find 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

Calculate Running Total in SQL Server 4