SQL Server Performance

Group By with Condition

Discussion in 'SQL Server 2008 General Developer Questions' started by subrata.bauri, Jun 9, 2011.

  1. subrata.bauri New Member

    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

    Grp by Candition.JPG
  2. FrankKalis Moderator

    Not sure I understand your requirement, but if you want to avoid 352 altogether add this condition to a WHERE clause to ignore these rows before grouping or filter it out using a HAVING clause to filter it out after grouping.
  3. subrata.bauri New Member

    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!
  4. subrata.bauri New Member

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

    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 

Share This Page