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.

#### 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

• ### Open source database use is a growing trend

Study shows that more users are using open source software for data management, with most organizations using more than one ...

• ### Databricks contributes Delta Lake to the Linux Foundation

Databricks has found a new home at the Linux Foundation for its open source Delta Lake data lake project, in a bid to help grow a...

• ### Apache Rya matures open source triple store database

Open source triple store database technology used by the U.S. Navy moves forward as a stable, mature project at the Apache ...

Teradata unveiled Vantage Analyst and Customer experience, two new tools now part of the vendor's analytics platform, on Tuesday ...

• ### SAS analytics platform aided by \$1 billion AI investment

The SAS suite of business intelligence products is quickly seeing results from the vendor's \$1 billion commitment to AI seven ...

• ### ThoughtSpot BI platform an early adopter of AI

Due to AI, ThoughtSpot's analytics tools have been accessible to citizen data scientists from the start, Andrew Yeung, the ...

## SearchSAP

New SAP co-CEOs Jennifer Morgan and Christian Klein must clarify SAP's products strategy, explain the experience economy and help...

• ### CEO of SAP Bill McDermott abruptly resigns

Bill McDermott, CEO of longtime ERP, CRM and BI software vendor SAP, resigned Thursday triggering a succession plan that promotes...

• ### What is an SAP Intelligent Enterprise? Don't look for a clear answer

What does Intelligent Enterprise actually mean? If the SAP term confuses you, you're not alone. Learn what the experts say and ...

## SearchSQLServer

• ### SQL Server database design best practices and tips for DBAs

Good database design is a must to meet processing needs in SQL Server systems. In a webinar, consultant Koen Verbeeck offered ...

• ### SQL Server in Azure database choices and what they offer users

SQL Server databases can be moved to the Azure cloud in several different ways. Here's what you'll get from each of the options ...

• ### Using a LEFT OUTER JOIN vs. RIGHT OUTER JOIN in SQL

In this book excerpt, you'll learn LEFT OUTER JOIN vs. RIGHT OUTER JOIN techniques and find various examples for creating SQL ...

## TheServerSide.com

• ### Is Java slow? Compared to C++, it's faster than you think

If you find that Java is slower than other languages, such as C++, here's how to better compare the two and the major differences...

• ### Java video games, like Minecraft, keep the language popular

The next generation of Java developers can learn the ins and outs of the language through an unlikely source: video games such as...

• ### Pivotal, Microsoft team up to deliver Azure Spring Cloud

Azure Spring Cloud, jointly developed by Microsoft and Pivotal, lets Spring developers bring apps to the cloud without concern ...

## SearchDataCenter

• ### Improve server rack physical security with ISO standards

Safeguarding information access is essential to data center operations. With ISO Standard 27001 and the right locking mechanisms,...

• ### Essential private cloud migration steps

Executive buy-in, application evaluation and tool configuration are all important parts of cloud selection. With these steps, ...

• ### IBM quantum computers' usefulness in sight -- using binoculars

IBM's Bob Sutor discusses Big Blue's new quantum systems and computation center, the realities of quantum computing today and how...

## SearchContentManagement

• ### Alfresco Software acquires SAP connector company Pernexas

The acquisition aims to make gaining new customers easier by providing another option to SAP customers looking for a cloud-native...

• ### 5 tips to avoid content sprawl when using Microsoft Teams

Businesses should heed this advice when rolling out Microsoft Teams to avoid content sprawl and ensure users can easily find the ...

• ### WebPurify launches profanity filter for memes and images

WebPurify has launched a beta of its Optical Character Recognition Profanity Filter Service to identify and block profane images ...

## SearchHRSoftware

• ### A backlash emerges over automated interviewing

Illinois may have adopted the nation's first automated hiring law, which will take effect Jan 1. The law is likely to become a ...

• ### Get smarter about HR data security

HR deals with mountains of sensitive employee data and must do a better job of protecting that information. Here's a look at how ...

• ### Walmart, FedEx make case for VR training

The payback from virtual training may be improved retention and shorter training times. An hour-long training period can be ...

Close