Carry value from between rows oracle

Question

I have a table like:

ID    MONTH     VALUE
1     06/2014    3
1     07/2014   -2
1     08/2014    1
2     03/2014    1
2     04/2014   -1
(...)

What I want is to create a new column which hierarchically sum the values, like:

ID    MONTH     VALUE    BALANCE
1     06/2014    3        3      <--  3 + "0" (no previous)
1     07/2014   -2        1      <-- -2 + 3   (previous balance plus current value)
1     08/2014    1        2      <--  1 + 1   (previous balance plus current value)
2     03/2014    1        1      <--  (...)
2     04/2014   -1
(...)

Possibly a way to use a connect by clause here, just couldn't get my head around it.

I am using Oracle 11gR2

Ideas?

Answer 1

The analytical function sum (...) over (...) is the perfect candidate:

create table tq84_t (
  id    number,
  month date,
  value number
);

insert into tq84_t values (1, date '2014-06-01',  3);
insert into tq84_t values (1, date '2014-07-01', -2);
insert into tq84_t values (1, date '2014-08-01', -1);

insert into tq84_t values (2, date '2014-03-01',  1);
insert into tq84_t values (2, date '2014-04-01', -1);


select
  id,
  month,
  value,
  sum(value) over (partition by id order by month) balance
from
  tq84_t;