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 one column and I'm joining another column called total sales. I want my results to show

Product Total for both products
Apple/Orange $500

I hope someone can help.

Thank you

 

[gmmastros]

It's hard to say without more information, but.... take a lok at this example.

Declare @Temp Table(ProductName VarChar(20), SalesAmt Decimal(10,2))

Insert Into @Temp Values('Apple', 100.00)
Insert Into @Temp Values('Apple', 200.00)
Insert Into @Temp Values('Apple', 300.00)
Insert Into @Temp Values('Orange', 400.00)
Insert Into @Temp Values('Orange', 500.00)

Select Coalesce(ProductName, 'Apple/Orange') As ProductName, 
       Sum(SalesAmt) As TotalSales
From   @Temp
Group By ProductName With Rollup

Copy/Paste the code above in to a query window and run it to see how this works.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Nova980]

This is what I have to pull 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

 

[gmmastros]

It looks to me like you are NOT using Microsoft SQL Server. As such, you may get better advice by posting your question is a forum specific to your database.

The simplest SQL Server solution would be to union all 2 queries, like this:

Declare @Temp Table(ProductName VarChar(20), SalesAmt Decimal(10,2))

Insert Into @Temp Values('Apple', 100.00)
Insert Into @Temp Values('Apple', 200.00)
Insert Into @Temp Values('Apple', 300.00)
Insert Into @Temp Values('Orange', 400.00)
Insert Into @Temp Values('Orange', 500.00)
Insert Into @Temp Values('Grape', 600.00)
Insert Into @Temp Values('Grape', 700.00)

Select ProductName, Sum(SalesAmt) As TotalSales
From   @Temp
Group By ProductName

Union All

Select 'Apple/Orange', Sum(SalesAmt) As TotalSales
From   @Temp
Where  ProductName In ('Apple','Orange')

Notice how the 2nd query filters on just two of the products. There's no group by because we want just one row in this result set. The union all will combine the results from the two individual queries.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Nova980]

My apologies, wrong forum. Thank you for your help