Hello experts,
I have this below requirement:
I have a simple table and I would like to sum-up the vals in that till the total sum is higher than the value that is specified. Eg:
with inputs as
(
select level vals from dual connect by level <=20
order by dbms_random.value
)
select vals from inputs;
In the above I have to sum up like below:
1. First order it by vals
2. The preset value is 10. That means I have to sum up all the vals a little over 10.
Eg: vals 1+2+3+4 is 10. And hence take one more val from the table so, 1+2+3+4+5 = 15. Then display it as sum and the count of rows that were used.
3. Now, start it from the second row,
Eg: 2+3+4+5 = 14. And hence display it
So, the output should be
val sum ct
1
2
3
4 15 4
2
3
4
5 14 4
3
4
5 12 3
And so on..
Note: The vals considered should not themselves be 10 or more
I know this can be achieved using match_recognize. But I am stuck.
Regards,