Jan 50 Feb 100 March 20I 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.
Requires Free Membership to View
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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation