asked on

SQL Oracle Running Total

Hello Experts Exchange
I have a table with Year, Income_Total.

I want a Running total on my select statement.

So it should look like this.

Year Income_Total Running_Total
-------------- --------------------- ----------------------
2013 2000 2000
2014 1500 3500
2015 500 4000

Can you give me the SQL Statement to achive this please?

Regards

SQLSearcher

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SQLSearcher

ASKER

Thank you very much.

I ended up with a query like this;

select year, AP_Period_Number, Sum(income_total) as Income_Total,Sum(sum(income_total)) over(order by year, AP_Period_Number) as running_total
from LEGACY_INCOME_SUMMARY
Where Year = 2014
Group by Year,AP_Period_Number
Union All
select year, AP_Period_Number, Sum(income_total) as Income_Total,Sum(sum(income_total)) over(order by year, AP_Period_Number) as running_total
from LEGACY_INCOME_SUMMARY
Where Year = 2015
Group by Year,AP_Period_Number
order by Year,AP_Period_Number;

slightwv (䄆 Netminder)

I didn't test this but try to simplify what you have. You shouldn't need to hit the table twice.

select year, AP_Period_Number, income_total,Sum(income_total) over(order by year, AP_Period_Number) running_total 
from
(
select year, AP_Period_Number, Sum(income_total) as Income_Total
from LEGACY_INCOME_SUMMARY
Where Year in (2014, 2015)
Group by Year,AP_Period_Number
)
order by Year,AP_Period_Number;

Open in new window

PortletPaul

perhaps they are wanting running total to reset at beginning of each year, so need to PARTITION as well as order

e.g.

select
year, month, val, sum(val) over(partition by year order by month)
from (
select year, month, sum(val) val
from table1
group by year, month
)

| YEAR | MONTH | VAL | SUM(VAL)OVER(PARTITIONBYYEARORDERBYMONTH) |
|------|-------|-----|-------------------------------------------|
| 2014 |     1 | 100 |                                       100 |
| 2014 |     2 | 100 |                                       200 |
| 2014 |     3 | 100 |                                       300 |
| 2014 |     4 | 100 |                                       400 |
| 2014 |     5 | 100 |                                       500 |
| 2014 |     6 | 100 |                                       600 |
| 2015 |     1 | 100 |                                       100 |
| 2015 |     2 | 100 |                                       200 |
| 2015 |     3 | 100 |                                       300 |
| 2015 |     4 | 100 |                                       400 |
| 2015 |     5 | 100 |                                       500 |
| 2015 |     6 | 100 |                                       600 |

CREATE TABLE Table1
    (YEAR int, MONTH int, VAL int)
;

INSERT ALL 
    INTO Table1 ("YEAR", "MONTH", "VAL")
         VALUES (2014, 1, 100)
    INTO Table1 ("YEAR", "MONTH", "VAL")
         VALUES (2014, 2, 100)
    INTO Table1 ("YEAR", "MONTH", "VAL")
         VALUES (2014, 3, 100)
    INTO Table1 ("YEAR", "MONTH", "VAL")
         VALUES (2014, 4, 100)
    INTO Table1 ("YEAR", "MONTH", "VAL")
         VALUES (2014, 5, 100)
    INTO Table1 ("YEAR", "MONTH", "VAL")
         VALUES (2014, 6, 100)
    INTO Table1 ("YEAR", "MONTH", "VAL")
         VALUES (2015, 1, 100)
    INTO Table1 ("YEAR", "MONTH", "VAL")
         VALUES (2015, 2, 100)
    INTO Table1 ("YEAR", "MONTH", "VAL")
         VALUES (2015, 3, 100)
    INTO Table1 ("YEAR", "MONTH", "VAL")
         VALUES (2015, 4, 100)
    INTO Table1 ("YEAR", "MONTH", "VAL")
         VALUES (2015, 5, 100)
    INTO Table1 ("YEAR", "MONTH", "VAL")
         VALUES (2015, 6, 100)
SELECT * FROM dual
;

Open in new window

http://sqlfiddle.com/#!4/3ac60/1

slightwv (䄆 Netminder)

>>so need to PARTITION as well as order

Probably. Thanks for catching that!!!