How to Sum a data with outer Apply ?

_{Monday, August 19, 2013 3:15 AM}

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:59 AM ✅Answered}

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 4:05 AM ✅Answered}

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