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.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- 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.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
-- 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.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- 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
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- 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
Greart Job Suresh. Thank you so much.
Please post an article about Dynamic SQL and where we can use that one.