Conditional SUM on Oracle

As an alternative to recursive SQL, you can also use the SQL MODEL clause. Personally, I find this a little easier to read than recursive SQL, though it is harder to write (because most people, like me, need to look up the syntax).

-- "test_data" is just a substitute for your real table, which I don't have-- it is just so people without your table can run this example and would-- not be part of your real solution.with test_data ( sort_col, addend ) as( SELECT 'A', 3 FROM DUAL UNION ALL SELECT 'B', 7 FROM DUAL UNION ALL SELECT 'C', 6 FROM DUAL UNION ALL SELECT 'D', 5 FROM DUAL UNION ALL SELECT 'E', 9 FROM DUAL UNION ALL SELECT 'F', 3 FROM DUAL UNION ALL SELECT 'G', 8 FROM DUAL ),-- Solution begins heresorted_inputs ( sort_col, sort_order, addend, running_sum_max_15) as( SELECT sort_col, row_number() over ( order by sort_col ) sort_order, addend, 0 from test_data )SELECT sort_col, addend, running_sum_max_15from sorted_inputsmodel dimension by (sort_order)measures ( sort_col, addend, running_sum_max_15 )rules update( running_sum_max_15[1] = addend[1],  running_sum_max_15[sort_order>1] =           case when running_sum_max_15[CV(sort_order)-1] < 15 THEN              running_sum_max_15[CV(sort_order)-1] ELSE 0 END+addend[CV(sort_order)])

RESULTS

+----------+--------+--------------------+| SORT_COL | ADDEND | RUNNING_SUM_MAX_15 |+----------+--------+--------------------+| A        |      3 |                  3 || B        |      7 |                 10 || C        |      6 |                 16 || D        |      5 |                  5 || E        |      9 |                 14 || F        |      3 |                 17 || G        |      8 |                  8 |+----------+--------+--------------------+

Using recursive cte:

DROP TABLE tab;CREATE TABLE tabASSELECT 'A' as col1, 3 AS col2 FROM dual UNION ALLSELECT 'B' as col1, 7 AS col2 FROM dual UNION ALLSELECT 'C' as col1, 6 AS col2 FROM dual UNION ALLSELECT 'D' as col1, 5 AS col2 FROM dual UNION ALLSELECT 'E' as col1, 9 AS col2 FROM dual UNION ALLSELECT 'F' as col1, 3 AS col2 FROM dual UNION ALLSELECT 'G' as col1, 8 AS col2 FROM dual;

Actual query:

WITH cte_r AS (  SELECT t.*, ROW_NUMBER() OVER(ORDER BY t.col1) AS rn FROM tab t), cte(col1, col2, total, rn) AS (  SELECT col1, col2, col2 AS total, rn  FROM cte_r  WHERE rn = 1  UNION ALL  SELECT cte_r.col1, cte_r.col2,       CASE WHEN cte.total >= 15 THEN 0 ELSE cte.total END + cte_r.col2 AS total,       cte_r.rn  FROM cte  JOIN cte_r    ON cte.rn = cte_r.rn-1)SELECT col1, col2, totalFROM cteORDER BY rn;

Output:

┌──────┬──────┬───────┐│ COL1 │ COL2 │ TOTAL │├──────┼──────┼───────┤│ A    │    3 │     3 ││ B    │    7 │    10 ││ C    │    6 │    16 ││ D    │    5 │     5 ││ E    │    9 │    14 ││ F    │    3 │    17 ││ G    │    8 │     8 │└──────┴──────┴───────┘

db<>fiddle demo

This solution is not limited to only Oracle but it will work on other RDBMSes such as SQL Server/PostgreSQL/MySQL 8.0/SQLite 3.25.

db<>fiddle demo - PostgreSQL

It is possible to achieve desired result much easier than recursive CTE.

Oracle 12c supports MATCH_RECOGNIZE and it is a good fit to solve "bin fitting" problem:

SELECT Col1, col2, rolling_sum, bin_numFROM TMATCH_RECOGNIZE (  ORDER BY col1  MEASURES SUM(col2) ROLLING_SUM, MATCH_NUMBER() AS bin_num  ALL ROWS PER MATCH  AFTER MATCH SKIP PAST LAST ROW  PATTERN ( A+ )  DEFINE A AS SUM(col2) < 15 + A.col2);

db<>fiddle demo

Output:

┌───────┬───────┬──────────────┬─────────┐│ COL1  │ COL2  │ ROLLING_SUM  │ BIN_NUM │├───────┼───────┼──────────────┼─────────┤│ A     │    3  │           3  │       1 ││ B     │    7  │          10  │       1 ││ C     │    6  │          16  │       1 ││ D     │    5  │           5  │       2 ││ E     │    9  │          14  │       2 ││ F     │    3  │          17  │       2 ││ G     │    8  │           8  │       3 │└───────┴───────┴──────────────┴─────────┘

Extras: Capping a runnig total with MODEL