none
How to Sum a data with outer Apply ?

    Question

  • Dear All,

    May i know how to sum a data with Outer apply ?  when i group the data it show the error , kindly advise if there is the Structure problem. thank  you

    SUM(BOXINFO.Quantity) AS TTL_QTY

      select          
                 
                 GRPDATA.Carton_Id
                 ,GRPDATA.Lot_Id
                 ,GRPDATA.Product_Id
                 ,GRPDATA.TTL_QTY
                 ,CAST(REPLACE(GRPDATA.MCLT_Top,',','') AS FLOAT) AS MCLT_Top
                 ,CAST(REPLACE(GRPDATA.MCLT_Bot,',','') AS FLOAT) AS MCLT_Bot
                 ,GRPDATA.RS_Top
                 ,GRPDATA.RS_Bot
                 ,GRPDATA.Oxi_Bot
                 ,CAST(REPLACE(GRPDATA.CS_Bot,',','') AS FLOAT) AS CS_Bot
                 ,GRPDATA.INGOT_SQUARENESS 
                 ,GRPDATA.DiagonalLength_Ave
                 ,GRPDATA.DiagonalLength_Std
                  ,GRPDATA.FlatLengthX_Ave
                  ,GRPDATA.FlatLengthX_Std
                  ,GRPDATA.FlatLengthY_Ave              
                  ,GRPDATA.FlatLengthY_Std
                   ,GRPDATA.FlatLenghtTaper_Ave
                  ,GRPDATA.FlatLenghtTaper_Std
                  ,GRPDATA.CornerLength_Ave
                  ,GRPDATA.CornerLength_Std
                 ,GRPDATA.Thickness_Ave
                 ,GRPDATA.Thickness_Std
                   ,GRPDATA.TTV
                   ,GRPDATA.Roughness_RA
                  ,GRPDATA.Roughness_RZ
                    FROM
                  ( SELECT  
                    ROW_NUMBER() OVER(PARTITION BY BOXINFO.Carton_Id,BOXINFO.Lot_Id ORDER BY BOXINFO.Lot_Id DESC) AS Row
                   ,BOXINFO.Carton_Id
                     ,BOXINFO.Lot_Id
                   ,BOXINFO.Product_Id
                   ,SUM(BOXINFO.Quantity) AS TTL_QTY
                   ,COADET.Top_Life_Time AS MCLT_Top
                  ,COADET.Bot_Life_Time AS MCLT_Bot
                    ,COADET.TOP_RS AS RS_Top
                     ,COADET.BOT_RS AS RS_Bot
                  ,COADET.Bot_Oxi AS Oxi_Bot
                   ,COADET.Bot_Cs AS CS_Bot
                    ,COADET.RIGHT_ANGLE AS INGOT_SQUARENESS 
                   ,ROUND(SORTERDET.DIAMETER_AVE,2) AS DiagonalLength_Ave
                    ,ROUND((CAST(SORTERDET.DIAMETER_3SIGMA AS FLOAT) / 3),3)  AS DiagonalLength_Std
                     ,ROUND(SORTERDET.Parallelism_D1_AVE,2) AS FlatLengthX_Ave
                     
                     ,ROUND((CAST(SORTERDET.Parallelism_D1_3SIGMA AS FLOAT) / 3),3)  AS FlatLengthX_Std
                   ,ROUND(SORTERDET.BtmParallelism_D1_AVE,2) AS FlatLengthY_Ave
                     ,ROUND((CAST(SORTERDET.BtmParallelism_D1_3SIGMA AS FLOAT) / 3),3)  AS FlatLengthY_Std
                     ,ROUND(SORTERDET.PARALLELISMX_AVE,2) AS FlatLenghtTaper_Ave
                    ,ROUND((CAST(SORTERDET.PARALLELISMX_3SIGMA AS FLOAT) / 3),3)  AS FlatLenghtTaper_Std
                     ,ROUND(SORTERDET.ChamferYX_TL_AVE,2) AS CornerLength_Ave
                    ,ROUND((CAST(SORTERDET.ChamferYX_TL_3SIGMA AS FLOAT) / 3),3)  AS CornerLength_Std
                   ,ROUND(SORTERDET.CenterThick_AVE,2) AS Thickness_Ave
                  ,ROUND((CAST(SORTERDET.CenterThick_3SIGMA AS FLOAT) / 3),3)  AS Thickness_Std
                    ,ROUND(SORTERDET.TTV_AVE,2) AS TTV
                     ,ROUND(SORTERDET.Ra_AVE,2) AS Roughness_RA
                    ,ROUND(SORTERDET.Rz_AVE,2) AS Roughness_RZ
                    
                  FROM [CIMProRPT01].[dbo].[SIM_PKG_BOXINFO] BOXINFO join [WHMSExcelRowData].[dbo].[CARTONID_ROWDATA] WHMSBOXINFO
                    
                  on BOXINFO.Carton_Id = WHMSBOXINFO.CARTONID 
                    
                    
                    
                    
                   OUTER APPLY
                    (SELECT * FROM  [CIMProRPT01].[dbo].[COA_DETAILS] COADET
                     WHERE COADET.LOT_ID = BOXINFO.Lot_Id)COADET
                    
                    
                     
                
                   OUTER APPLY
                    (SELECT TOP 1 * FROM  [MCData].[dbo].[MC_SORTER_DET] SORTERDET
                    WHERE SORTERDET.LOT_ID = BOXINFO.Lot_Id ORDER BY SORTERDET.CREATE_DATE DESC)SORTERDET
                    WHERE 1=1
                    
                    AND WHMSBOXINFO.Buyoffdate >= '20130813'
                    AND WHMSBOXINFO.buyoffdate  <= '20130813'
                    AND BOXINFO.Carton_Id IN (WHMSBOXINFO.CARTONID)
                  -- AND  WHMSBOXINFO.PART_NO LIKE '%NLNBR%'
                   -- AND WHMSBOXINFO.Status='Wait'
                   ) GRPDATA
    
    
            
    
    
                 WHERE GRPDATA.ROW < 2
                 
           
                   
             
              ORDER BY  CASE WHEN GRPDATA.MCLT_Top IS NULL OR GRPDATA.DiagonalLength_Ave IS NULL THEN 1 ELSE 0 END, CAST(REPLACE(GRPDATA.MCLT_Bot,',','') AS FLOAT)desc, CAST(REPLACE(GRPDATA.MCLT_Top,',','') AS FLOAT) desc,CAST(REPLACE(GRPDATA.CS_Bot,',','') AS FLOAT) ASC

    Monday, August 19, 2013 3:15 AM

Answers

  • It's hard to be too specific without a better description.  But SUM() is a aggregate function.  So you either need a group by clause or an OVER clause.  I suspect you want an OVER clause.  Since your ROW_NUMBER() function is partitioned by BOXINFO.Carton_Id,BOXINFO.Lot_Id, my guess is you want the sum partitioned by BOXINFO.Carton_Id,BOXINFO.Lot_Id.  That would be

    SUM(BOXINFO.Quantity) OVER(BOXINFO.Carton_Id,BOXINFO.Lot_Id) As TTL_QTY

    Tom

    Monday, August 19, 2013 3:59 AM
  • SUM is an aggregate function. You will need fields to group so as to get the sum of quantity. Therefore instead of "SUM(BOXINFO.Quantity)" you can try using a subquery as,     (NOT TESTED)

    (SELECT SUM(BOXINFO.Quantity) 
    FROM [CIMProRPT01].[dbo].[SIM_PKG_BOXINFO] A
    WHERE A.Carton_Id = BOXINFO.Carton_Id AND A.Lot_Id = BOXINFO.Lot_Id AND A.Product_Id = BOXINFO.Product_Id
    GROUP BY A.Carton_Id,A.Lot_Id,A.Product_Id) AS TTL_QTY


    Regards, RSingh

    Monday, August 19, 2013 4:05 AM

All replies

  • It's hard to be too specific without a better description.  But SUM() is a aggregate function.  So you either need a group by clause or an OVER clause.  I suspect you want an OVER clause.  Since your ROW_NUMBER() function is partitioned by BOXINFO.Carton_Id,BOXINFO.Lot_Id, my guess is you want the sum partitioned by BOXINFO.Carton_Id,BOXINFO.Lot_Id.  That would be

    SUM(BOXINFO.Quantity) OVER(BOXINFO.Carton_Id,BOXINFO.Lot_Id) As TTL_QTY

    Tom

    Monday, August 19, 2013 3:59 AM
  • SUM is an aggregate function. You will need fields to group so as to get the sum of quantity. Therefore instead of "SUM(BOXINFO.Quantity)" you can try using a subquery as,     (NOT TESTED)

    (SELECT SUM(BOXINFO.Quantity) 
    FROM [CIMProRPT01].[dbo].[SIM_PKG_BOXINFO] A
    WHERE A.Carton_Id = BOXINFO.Carton_Id AND A.Lot_Id = BOXINFO.Lot_Id AND A.Product_Id = BOXINFO.Product_Id
    GROUP BY A.Carton_Id,A.Lot_Id,A.Product_Id) AS TTL_QTY


    Regards, RSingh

    Monday, August 19, 2013 4:05 AM