# Need a sum on my current column

On an Oracle database, I need to calculate something like this - on column `my`

``````id yr    m    v1  v2 v3 st my
1  2015  10   1   0  0  0  0
2  2015  10   0   0  2  1  4
3  2015  10   2   0  0  1  0
4  2015  10   0   1  0  1  0
``````

When `st`=1, calculate 10 (total from m row) minus total in rows `v1`+`v2`+`v3` (6) = 4. So, in the second row I will put 4 and in the third row, the total of 10 is reached and I will put 0. The same in the 4th row. So I need to calculate a sum on rows until the current row. Can I do this?

The sum on `v1`+`v2`+`v3` is something like this:

``````(sum(v1+v2+v3) over (partition by yr))
``````

How can I find out the sum for the row I calculate `my` so I won't get over my maximum sum from `m`?

If I understand this correctly, what you want to do is successively subtract the value of `SUM(v1 + v2 + v3) OVER (PARTITION BY year)` from `m` for each row where `st` is 1, until `m` is exhausted; once the result drops to zero or below, it should show just `0`.

Since the subtracted value is the same, you could just enumerate all `st=1` rows and subtract `sum * row_number` for each row, like this:

``````WITH calculated AS (
SELECT
mytable.*,
m - SUM(v1 + v2 + v3) OVER (PARTITION BY yr)
* ROW_NUMBER()      OVER (PARTITION BY yr, st ORDER BY id ASC) AS res
FROM
mytable
)
SELECT
id,
yr,
m,
v1,
v2,
v3,
st,
CASE WHEN st = 1 AND res > 0 THEN res ELSE 0 END AS my
FROM
calculated
;
``````

The above assumes that `m` is the same across all rows of a partition.