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 (
    m - SUM(v1 + v2 + v3) OVER (PARTITION BY yr)
      * ROW_NUMBER()      OVER (PARTITION BY yr, st ORDER BY id ASC) AS res
  CASE WHEN st = 1 AND res > 0 THEN res ELSE 0 END AS my

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.