CODEWITHSUNDEEP
sqloracle-databaseoracle11g
navid sedigh
navid sedigh

Reputation: 281

sum with a specific condition in select

I have a number for example: 1000 (1)

I have a query that returns different number without any order (2). for example: 100,300,1000,400,500,600

I want to write a query (not a loop) that sum my numbers in (2) till the sum be in the range of (1000-300 , 1000+ 300) -> (700,1300)

for example : 100+300+400 could be an answer or 400+500 or ...

P.S : the first order of numbers that is in that range is an answer.

Upvotes: 1

Views: 74

Answers (1)

vishnudattan
vishnudattan

Reputation: 476

Not sure if I understood your question fully, but you may be able to achieve this using the windowing clause of analytic functions.

I created a sample table number_list with the values you'd provided. Assuming (2) to be the output from below query ..

    SQL> select * from number_list;

    VALUE
    ----------
    100
    300
    1000
    400
    500
    600


    6 rows selected.

.. you now need the first list of numbers who's sum falls within a certain range i.e. (1000 - 300) and (1000 + 300) ..

    SQL> with sorted_list as
      2      (
      3          select rownum rnum, value from
      4                     ( select value from number_list order by value ) -- sort values ascending
      5      )
      6  select value from sorted_list where rnum <= (
      7      select min(rnum) from ( -- determine first value from sorted list to fall in the specified range
      8          select rownum rnum, value,
      9              sum(value) over ( order by null
     10                                rows between
     11                                unbounded preceding -- indicate that the window starts at the first row
     12                                and current row -- indicate that the window ends at the current row
     13                              ) sum
     14              from sorted_list
     15          ) where sum between (1000-300) and (1000+300)
     16      );

         VALUE
    ----------
           100
           300
           400

Upvotes: 2

Related Questions