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
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 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 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