oracle - Using a sum to return a number of records -

i trying return number of records based on page count added records selected. page counts vary, each batch returned have page count falls in range. ..

data  print id      page_count   status 1000          50           ready 1001          50           ready 1002          50           ready 1003          75           ready  select print_id, page_count,status print_job status = 'ready' , sum (page_count)>100 , sum(page_count)<200  returned  print_id      page count   status 1000          50           ready 1001          50           ready 1002          50           ready 

i have tried having , using sub query. seems bring individual pieces have page counts fall in range, not sum total number of pages. ideas?

if question can reformulated in grouping records in order of print_id, each group has sum(page_count) > 100 (or rest of records @ end of table); here 1 solution using recursive subquery factoring

in first step add sequential id table ordered same print_id, used in recursion.

create table  tab_data select print_id, page_count, row_number() on (order print_id) id data;  select * tab_data order id;    print_id page_count         id ---------- ---------- ----------       1000         50          1        1001         50          2        1002         50          3        1003         75          4  

the initial subquery sets accumalator acc 0 , initialze first group. recursive subquery performs logic of checking limit , increasing group id.

with dt(id, print_id, page_count, acc, grp) (  select id, print_id, page_count, 0 acc, 1 grp tab_data id = 1 union  select tab_data.id, tab_data.print_id, tab_data.page_count,   -- accumulate   case when     dt.acc+dt.page_count >  100 /* new group, reset acc */ 0    else dt.acc+dt.page_count end acc,   -- define group   case when     dt.acc+dt.page_count >  100 /* increase group */ grp+1    else grp end grp  tab_data, dt  tab_data.id=dt.id+1) select * dt ; 

returns requested

        id   print_id page_count        acc        grp ---------- ---------- ---------- ---------- ----------          1       1000         50          0          1           2       1001         50         50          1           3       1002         50        100          1           4       1003         75          0          2  

i'm not considering second limit <200. hope provides idea how refine rules implement it.