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:
----------- ---------- ------------
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 last published in March 2002

Dig Deeper on Oracle and SQL

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.