How do I display the combinations and their summation values in a SQL Statement?

The Oracle (tm) Users' Co-Operative FAQ

How do I display the combinations and their summation values in a SQL Statement?

Back to index of questions

The following SQL query pattern can be used to display all the combinations when their summation value matches the user’s requirement.

SQL> create table test as

     select 1 AS id, chr(ascii('A')+n-1) as item_name, n as item_value     

     from (select level n from dual connect by level <=8);

SQL> select * from test;

        ID I ITEM_VALUE

---------- - ----------

         1 A          1

         1 B          2

         1 C          3

         1 D          4

         1 E          5

         1 F          6

         1 G          7

         1 H          8

SQL> variable LowerBound  number

SQL> variable UpperBound  number

SQL> exec :LowerBound  := &lowerBound

Enter value for lowerbound: 2

SQL> exec :UpperBound  := &upperBound

Enter value for upperbound: 3

SELECT id, path , 

       SUM(TO_NUMBER(SUBSTR(str,

                      INSTR(str, ',', 1, LEVEL  ) + 1,

                      INSTR(str, ',', 1, LEVEL+1) -

          INSTR (str, ',', 1, LEVEL) -1 ) )) sum_value

 FROM

(SELECT id, SYS_CONNECT_BY_PATH (item_value , ',')||',' AS str,

            REPLACE(SYS_CONNECT_BY_PATH (item_name, ','), ',') AS path

   FROM test

   CONNECT BY PRIOR id = id

   AND item_name > PRIOR item_name

   AND item_value <=:UpperBound

)

CONNECT BY PRIOR path = path

AND INSTR (str, ',', 1, LEVEL+1) > 0

AND PRIOR dbms_random.string ('p', 10) IS NOT NULL

GROUP BY id,path

HAVING SUM(TO_NUMBER(SUBSTR(str,

                      INSTR(str, ',', 1, LEVEL  ) + 1,

                      INSTR(str, ',', 1, LEVEL+1) -

           INSTR (str, ',', 1, LEVEL) -1 ) )) 

      BETWEEN :LowerBound AND :UpperBound

ORDER BY  sum_value

ID    PATH      SUM_VALUE                                                          

---  --------   ----------                                                                                                                        

 1    B         2

 1    AB        3                                             

 1    C         3                                                

SQL> spool off;

Back to top

Back to index of questions