oracle grouping everytime the sum amount is below 15

I’ve following data:

SELECT 1 note, 1000 amt FROM dual union all
SELECT 2 note, 2000 amt FROM dual union all
SELECT 3 note, 8000 amt FROM dual union all
SELECT 4 note, 3000 amt FROM dual union all
SELECT 5 note, 1500 amt FROM dual union all
SELECT 6 note, 1600 amt FROM dual union all
SELECT 7 note, 20000 amt FROM dual union all
SELECT 8 note, 20000 amt FROM dual union all
SELECT 9 note, 2100 amt FROM dual union all
SELECT 10 note, 4500 amt FROM dual union all
SELECT 11 note, 1000 amt FROM dual union all
SELECT 12 note, 16000 amt FROM dual

and I need sum the amount, but for every sum <= 15000, they will be grouped together. If the amount is > 15000, they will be on their own group like this:

NOTE	AMT	group
1	1000	1
11	1000	1
5	1500	1
6	1600	1
2	2000	1
9	2100	1
4	3000	1
10	4500	2
3	8000	2
12	16000	3
7	20000	4
8	20000	5

I need the solution in oracle sql, is it possible? I’m using oracle 11g

Answer

One method is a recursive subquery:

with tt(note, amt, seqnum) as (
      select t.note, t.amt, row_number() over (order by amt) as seqnum
      from t
     ),
     cte(note, amt, seqnum, grp, running_amt) as (
      select note, amt, seqnum, 1, amt
      from tt
      where seqnum = 1
      union all
      select tt.note, tt.amt, tt.seqnum,
             (case when tt.amt + cte.running_amt > 15000 then cte.grp + 1 else cte.grp end),
             (case when tt.amt + cte.running_amt > 15000 then tt.amt else tt.amt + cte.running_amt end)
      from cte join
           tt
           on tt.seqnum = cte.seqnum + 1
     )
select *
from cte;

Here is a db<>fiddle.

Note: This is ordering by amt — as in your sample data. You can as easily order by note (which also makes sense) just by adjusting seqnum in tt.