Q
Problem solve Get help with specific problems with your technologies, process and projects.

Calculating running totals in SQL

I have aggregated table that stores monthly values. For example:

```Jan 50
Feb 100
March 20
```
I want following result:
```
Jan 50
Feb 150 (Sum of Jan+Feb)
March 170 (Sum Jan+Feb+Mar)
```
Is this possible using single SQL? I cannot use the ROLLUP function because my current version Oracle does not support it.

What I think you are asking for is a way to calculate running balances in SQL. I'll illustrate one solution with an example. Suppose you have a table of daily revenue amounts, like this:

```create table DailyRevenue
( Day date,
Amount number(7,2)
);

insert into DailyRevenue values ('25-FEB-2002',112);
insert into DailyRevenue values ('26-FEB-2002',84);
insert into DailyRevenue values ('27-FEB-2002',45);
insert into DailyRevenue values ('28-FEB-2002',73);
insert into DailyRevenue values ('01-MAR-2002',263);
insert into DailyRevenue values ('02-MAR-2002',198);
insert into DailyRevenue values ('03-MAR-2002',36);
```
The trick is to use a correlated sub-query to obtain, for each row, the sum of its amount and the amounts for all of its earlier rows. This may seem inefficient, when compared to a procedural approach, but you must remember that we are dealing with SQL, which is not a procedural language. This is what the query looks like:
```select a.Day, a.Amount,
( select sum(b.Amount) from DailyRevenue b
where b.Day <= a.Day ) RunningTotal
from DailyRevenue a
order by a.Day;
```
Here are the rows returned by the query:
```DAY             AMOUNT RUNNINGTOTAL
----------- ---------- ------------
25-FEB-2002        112          112
26-FEB-2002         84          196
27-FEB-2002         45          241
28-FEB-2002         73          314
01-MAR-2002        263          577
02-MAR-2002        198          775
03-MAR-2002         36          811
```

• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

This was last published in March 2002

Have a question for an expert?

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

SearchDataManagement

• AP uses data collaboration platform to spread data journalism

Data journalism reporters need tools that deliver quick context for stories on deadline. A data collaboration platform from ...

• GPU databases bring greater parallelism to big data processing

GPU databases offer a new way to process data. 451 Research analyst James Curtis discusses where they fit in big data ...

• Distributed SQL database wagered on for scalability boost, GDPR

CockroachDB is a distributed database system that seeks to support processing on a global scale, a need that online gambling ...

• Ten KPI templates for your dashboards

KPIs help companies gauge success, but how do you choose the right metrics to create useful reports? Here you'll find 10 KPI ...

• Data visualization process demands smart design, accurate data

Well-designed data visualizations can enable executives to make more-informed business decisions, increasing the potential ROI of...

• Seven good data visualization practices for visual integrity

Data visualizations need visual integrity to ensure that the data they present can be interpreted correctly. Follow these design ...

SearchSAP

• Inside SAP's digital transformation strategy

In this Q&A, Bertram Schulte, SAP's chief digital officer, discusses the how and why as the company goes through its own internal...

• SAP Cloud Platform pushes to break new ground, extend ERP

SAP wants its Cloud Platform to connect ERP systems with new technology like machine learning and IoT, resulting in new processes...

• SAP Cloud Platform architecture is glue for new technologies

SAP Cloud Platform has numerous integration tools for linking next-gen technologies like blockchain, machine learning and IoT to ...

SearchSQLServer

• Power BI Report Server adds new Power BI reporting options

Updates to Power BI Report Server expand the on-premises platform's reporting functionality and increase the number of data ...

• Azure Cosmos DB features, pricing morph with new provisioning model

The ability to provision Azure Cosmos DB throughput at the database level could come with pricing sticker shock for some users of...

• Why running SQL Server on Docker is no longer frowned upon

Microsoft now lets SQL Server databases run in Docker containers, a capability that depends on using volumes to store data in a ...

TheServerSide.com

• Why we still use Apache Ant tool in Java build and deploys

Plenty of companies still use the Apache Ant tool in Java development cycles and as part of their continuous deployments. Here's ...

• Packaging Java programs in the Docker and microservices age

The packaging of Java programs has taken an interesting turn, as the original JAR format becomes a favorite even for Java web ...

• How to calculate McCabe cyclomatic complexity in Java

An understanding of how to calculate McCabe cyclomatic complexity in Java is the first step in better testing and improved ...

SearchDataCenter

• Get to know SIEM software basics and benefits

Security information and event management tools give organizations enhanced network management functionality, such as automatic ...

• Plexxi buy bolsters HPE composable infrastructure strategy

HPE strengthened its composable infrastructure strategy with the acquisition of Plexxi, a startup that adds a networking element ...

• Hadoop cluster configuration best practices streamline workflows

Organizations that deal with a variety of Hadoop configurations can streamline workflows through baseline configuration, tests ...

SearchContentManagement

• Five areas to focus on in a SharePoint and GDPR compliance strategy

SharePoint admins need to be ready for the General Data Protection Regulation, too. Expert Reda Chouffani suggests five things to...

• Workplace collaboration tool bolsters employee productivity

The benefits of workplace collaboration tools trickle down to employee relationships, company growth, community engagement and ...

• New age collaboration tools empower the future of ECM

Document management, file-sharing and ECM platforms might represent 'ancient' 1990s technology, but AI and collaboration tools ...

SearchHRSoftware

• Huge potential of video interview software comes with risks

Tech-savvy millennials' advantage over older workers risks ageism, and AI needs work handling emotions and ethnicity. But few ...

• How to solve six of the biggest employee retention challenges

Recruiters are enticing workers with new opportunities on an increasingly frequent basis. Give your employees plenty of reasons ...

• Recruiting online giants turn to new technologies

Technology is driving major changes to the recruiting online market. Monster.com and CareerBuilder.com have deployed new job ...

Close