Combining two values in one column

[Nova980]

Hi,

I'm looking to combine two values in one column in my results. For example, I have a value of apple and a value of orange in PRDRST_L4_MAIN_DSC and I'm joining to pull specific ITEM_ID's however I need all ITEM_ID's that belong to Orange and Apple to sum SALES together in on row. Results would be something similar below:

Product Total for both products
Apple/Orange $500

This is what pulls the totals for each PRDRST_L4_MAIN_DSC. I want to combine only two PRDRST_L4_MAIN_DSC out of about 12.

select    a19.WK_NBR_ID  WK_NBR_ID,
    a11.STATE_ID  STATE_ID,
    a16.MAIN_DSC  MAIN_DSC,
    sum(a11.SALES_AMT)  TOTAL
from    SALES    a11
    join    ITEM_L    a12
      on     (a11.ITEM_ID = a12.ITEM_ID)
    join    PRDRST_L1_L    a13
      on     (a12.PRDRST_L1_ID = a13.PRDRST_L1_ID)
    join    PRDRST_L2_L    a14
      on     (a13.PRDRST_L2_ID = a14.PRDRST_L2_ID)
    join    PRDRST_L3_L    a15
      on     (a14.PRDRST_L3_ID = a15.PRDRST_L3_ID)
    join    PRDRST_L4_L    a16
      on     (a15.PRDRST_L4_ID = a16.PRDRST_L4_ID)
    join    PRDRST_L5_L    a17
      on     (a16.PRDRST_L5_ID = a17.PRDRST_L5_ID)
    join    TME_DAY_L    a18
      on     (a11.DAY_DT = a18.DAY_DT)
    join    TME_WMWK_L    a19
      on     (a18.WMWK_DT = a19.WMWK_DT)
where    (a18.DAY_DT between To_Date('2010-01-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and To_Date('2010-02-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
 and a17.PRDWMRST_L6_ID in (2)
 and a11.STATE_ID in (6))
group by  a19.WK_NBR_ID,
      a11.STATE_ID,
      a16.PRDRST_L4_MAIN_DSC

I hope someone can help.

Thank you

 

[Dagon]

Since it's only one instance you want to do this for, you could use something like:

SQL> create table fruit (id number, fruit varchar2(20), amount number);

Table created.

SQL> insert into fruit values (1, 'orange', 50);

1 row created.
 
SQL> insert into fruit values (2, 'orange', 10);

1 row created.
 
SQL> insert into fruit values (3, 'apple', 20);

1 row created.
 
SQL> insert into fruit values (4, 'banana', 45);

1 row created.
 
SQL> insert into fruit values (5, 'satsuma', 22);

1 row created.
 
SQL> select case when fruit in ('apple', 'orange') then 'apple/orange' else fruit end as fruit, sum(amount) as amount
  2  from fruit
  3  group by case when fruit in ('apple', 'orange') then 'apple/orange' else fruit end
  4  /

FRUIT                              AMOUNT
------------------------------ ----------
banana                                 45
apple/orange                           80
satsuma                                22

Retired (not by choice) Oracle contractor.