The Oracle (tm) Users' Co-Operative FAQ

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


Author's name: Frank Zhou

Author's Email:zhou328@comcast.net

Date written: 18th December 2005

Oracle version(s): 9.2

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 LowerBoundnumber

SQL> variable UpperBoundnumber

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 BYsum_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