Calculate Running Total, Total of a Column and Row

Calculate Running Total, Total of a Column and Row

18 Mar 2024
Intermediate
218K Views
5 min read

How to do Running Total: An Overview

You are often required to show information on each transaction and keep a Running Total and Final Total like GridView in Asp.Net. In this SQL Server, I am going to explain, how you can achieve this using SQL Query simply and easily.

Read More: SQL Server Interview Questions and Answers

Suppose you have the below CustomerOrders table and have the data as shown below:


CREATE TABLE CustomerOrders
(
 OrderID int identity,
 Amount Decimal(8,2),
 OrderDate SmallDatetime default getdate()
 )

Go
 
 INSERT INTO CustomerOrders(Amount) Values(120.12)
 INSERT INTO CustomerOrders(Amount) Values(20.12)
 INSERT INTO CustomerOrders(Amount) Values(10.12)
 INSERT INTO CustomerOrders(Amount) Values(30.12)
 INSERT INTO CustomerOrders(Amount) Values(40)

GO
 
 SELECT * FROM CustomerOrders

Output

Read More: Basics of SQL Commands

Calculating Running Total

Let's see how to calculate the running total using SQL Query as given below:


 select OrderID, OrderDate, CO.Amount
 ,(select sum(Amount) from CustomerOrders 
 where OrderID <= CO.OrderID)
 'Running Total'
from CustomerOrders CO

Output

Calculating Final Total

Let's see how to calculate the final total using ROLLUP within SQL Query as given below:


SELECT OrderID, SUM(Amount) AS Amount
FROM CustomerOrders
GROUP BY OrderID WITH ROLLUP

Output

Calculating the Total of All Numeric Columns in a Row

Let's see how to calculate the total of all numeric fields within a row using SQL Query as given below:


SELECT OrderID, Amount, SUM(OrderID+Amount) AS RowNumericColSum
FROM CustomerOrders
GROUP BY OrderID,Amount
ORDER BY OrderID

Output

What do you think?

I hope you will enjoy the tips while writing a query in SQL Server. If you want to gain a practical understanding, you can enroll in our SQL Server Course.

Do you Know?

.NET is gaining popularity day by day, especially after the release of .NET 8. .NET 8 is not only a framework version but much more than that. It redefines the way software applications are built and deployed, enabling developers to meet the evolving demands of modern computing.

Therefore, if you want to upskill yourselves and stand out from others consider our following training programs on .NET.

Take our free sqlserver skill challenge to evaluate your skill

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET CHALLENGE

Share Article
Batches Schedule
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Self-paced Membership
  • 22+ Courses
  • 750+ Hands-On Labs
  • 300+ Quick Notes
  • 55+ Skill Tests
  • 45+ Interview Q&A
  • 10+ Real-world Projects
  • Career Coaching
  • Email Support
Upto 66% OFF
KNOW MORE..

To get full access to all courses

Accept cookies & close this