Group By with Condition

Dear all,

I have a simple code (Part of a Procedure) given below :

Code:

update #temp
  set #temp.Quantity_mc = a.Total
from #temp Inner join
    (
      select psp_item_no,psp_item_var,sum(psp_qty_prpnl) as Total
      from pmddb..pmd_mpsp_ps_postn x,common..ims_variant_master y
--      WHERE
----      NOT EXISTS(SELECT * FROM common..ims_variant_master  Where  description like 'optional%' AND
----                  stock_no =(SELECT q.psp_item_no from pmddb..pmd_mpsp_ps_postn q where
----        --psp_ps_no =a.psp_ps_no and
--
--        x.psp_item_no  = y.stock_no AND
--        x.psp_item_var =y.variant_no and
--        x.psp_io_flag='o'

      group by psp_item_no,psp_item_var
    ) as a
      on item_code = a.psp_item_no AND
          variant_code=a.psp_item_var 

Thru this grouping I'm getting following Data set :

Code:

Psp_ps_no psp_item_no psp_item_var psp_qty_prpnl

6 DRR01DELT0004 ## 10.00000000

352 DRR01DELT0004 ## 10.00000000

353 DRR01DELT0004 ## 6.00000000

I want a condition which will avoid the 352 row because 352 has optional in its description field in another Table.
Sample Data is given below

Thanks for your reply .

Plz look my code below :

Code:

update #temp
  set #temp.Quantity_mc = a.Total
from #temp Inner join
    (
      select psp_item_no,psp_item_var,sum(psp_qty_prpnl) as Total
      from pmddb..pmd_mpsp_ps_postn
      group by psp_item_no,psp_item_var
    ) as a
      on item_code = a.psp_item_no AND
          variant_code=a.psp_item_var

This WAS my 1st code

Where #temp table getting Quantity_mc =26 against the item_code =DRR01DELT0004 because by this group I'm getting below data set :

Code:

Psp_ps_no psp_item_no  psp_item_var  psp_qty_prpnl
6        DRR01DELT0004  ##        10.00000000
352      DRR01DELT0004  ##        10.00000000
353      DRR01DELT0004  ##        6.00000000

Now I want that Psp_ps_no =352 should not come in this grouping becasue If I filter the pmddb..pmd_mpsp_ps table as below

Code:

Select psp_item_no from pmddb..pmd_mpsp_ps where
                  Psp_ps_no =352 and
                  psp_io_flag = 'o'

I'm getting psp_item_no = DRR24D and this Item code has OPTIONAL in its description column in the common..ims_variant_master table .

How to do rest of the things ? Kindly reply.

Thanks!

I have tried with this code and it seems to me working fine.

Code:

select psp_item_no,psp_item_var,sum(psp_qty_prpnl) as Total

  from pmddb..pmd_mpsp_ps_postn pmd

    where NOT EXISTS (SELECT * From common..ims_variant_master

                                ims,pmddb..pmd_mpsp_ps_postn pmd2

                                where description like 'Optional%' and

                                ims.stock_no= pmd2.psp_item_no and

                                pmd2.psp_ps_no = pmd.psp_ps_no and

                                pmd2.psp_io_flag = 'o'

                        )
  
group by psp_item_no,psp_item_var