Tutorial Gateway

  • C Language
  • Java
  • R
  • SQL
  • MySQL
  • Python
  • BI Tools
    • Informatica
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • QlikView
  • Js

Calculate Running Total in SQL

by suresh

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

Placed Under: SQL, SQL Interview Questions

Trending Posts

SQL ERROR SEVERITY

C log10 Function

JavaScript TRIM

SQL IF ELSE

Cached Reports in SSRS

R ggplot2 Dot Plot

SQL JOINS

Python Program to find Volume and Surface Area of a Cube

Python Program to find roots of a Quadratic Equation

Python Reverse List Function

  • C Programs
  • Java Programs
  • SQL FAQ’s
  • Python Programs
  • SSIS
  • Tableau
  • JavaScript

Copyright © 2019 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy