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