Q

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

For More Information

  • What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
  • 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 your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • 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 first published in March 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

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

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

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close