I have aggregated table that stores monthly values. For example:
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
Jan 50 Feb 100 March 20
Jan 50 Feb 150 (Sum of Jan+Feb) March 170 (Sum Jan+Feb+Mar)
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);
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;
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.