I have aggregated table that stores monthly values. For example:
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.
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.