Post
Ask Question

How To Find Running Total And Final Total In SQL Server

Here, we will explain how to find Running Total and Final Total or Grand Total in SQL Server.
 
For example, you have the below given #tblEmployee temporary table and has the data, as shown below.
  1. CREATE TABLE #tblEmployee  
  2. (  
  3.    EmpID int identity,  
  4.    Salary Decimal(8,2),  
  5.    Grade varchar(10)  
  6. )  
Insert some values in a temporary table like this.
  1. INSERT INTO #tblEmployee(Salary,Grade) Values(10000.51,'B')  
  2. INSERT INTO #tblEmployee(Salary,Grade) Values(20000.12,'B')  
  3. INSERT INTO #tblEmployee(Salary,Grade) Values(25000.02,'B')  
  4. INSERT INTO #tblEmployee(Salary,Grade) Values(9000.28,'C')  
  5. INSERT INTO #tblEmployee(Salary,Grade) Values(7000,'C')  
  6. INSERT INTO #tblEmployee(Salary,Grade) Values(6000,'C')  

Find the Running Total in the SQL Server

 
Write the following SQL Query to find the running total for the Salary column from #tblEmployee temporary table.
  1. SELECT EmpID, Grade, Emp.Salary ,(SELECT SUM(Salary) FROM #tblEmployee WHERE EmpID <= Emp.EmpID) 'Running Total' FROM #tblEmployee Emp  
I have used the sub-query to find the running total.
 
Following Result produce by this SQL Query,
 

Find the Final Total or Grand Total in SQL Server

 
Write the following SQL Query to find the final total or grand total for the Salary column from #tblEmployee temporary table.
  1. SELECT EmpID, SUM(Salary) AS Salary FROM #tblEmployee GROUP BY EmpID WITH ROLLUP  
Here I used ROLLUP to achieve the desired result.
 

What is ROLLUP

 
The ROLLUP is an extension of the GROUP BY clause. The ROLLUP option allows you to include extra rows that represent the subtotals, which are commonly referred to as super-aggregate rows, along with the grand total row. By using the ROLLUP option, you can use a single query to generate multiple grouping sets.
 
Following Result produce by this SQL Query,
 
So, in this blog, we tried to explain how to find the running total from the SQL table. I hope this blog helps all of us. It is also most asked question from the interview.