Hi experts,
i have a concern regarding with my query i'm getting negative figures when i use SUM to group by my field. query below.
query results:
Item Name Category Invoice Qty. Cancelled Qty. Balance Qty. Weight
Item 50Kg Category1 2 1 1 -50
Item 50kg Category2 5 2 3 -150
SELECT T1.[Dscription], T2.U_SMC_Sub_Category As 'Sub Category', SUM(T1.Quantity) As 'Invoice Quantity', SUM(T3.Quantity) AS 'Cancelled Qty.', SUM(T1.[Quantity]) -SUM( T3.Quantity) As 'Balance Qty.', SUM(T1.Quantity) -SUM(T3.Quantity) * SUM(T2.U_SCM_Weight) As 'Weight', SUM(T1.Quantity) -SUM(T3.Quantity) * SUM(T2.U_SMC_Volume) As 'Volume', SUM(T1.Quantity) -SUM(T3.Quantity) * SUM(T2.U_SMC_Seeds) As 'Bag', SUM(T1.Quantity) -SUM(T3.Quantity) * SUM(T2.U_SMC_Pack) As 'Pack', SUM(T1.Quantity) -SUM(T3.Quantity) * SUM(T2.U_SMC_Can) As 'Can' FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] LEFT OUTER JOIN RIN1 T3 ON T3.BaseEntry = T1.DocEntry AND T3.BaseType='13' and T3.BaseLine= T1.LineNum INNER JOIN OITB T4 ON T2.[ItmsGrpCod] = T4.[ItmsGrpCod] WHERE T0.[DocDate] BETWEEN [%0] AND [%1] AND T0.CANCELED = 'N' GROUP BY T1.[Dscription], T2.U_SMC_Sub_Category
Thanks a lot,
Hi Erwin,
Try this:
SELECT T1.[Dscription], T2.U_SMC_Sub_Category As 'Sub Category', SUM(T1.Quantity) As 'Invoice Quantity', SUM(T3.Quantity) AS 'Cancelled Qty.', SUM(T1.[Quantity]) - SUM( T3.Quantity) As 'Balance Qty.', (SUM(T1.Quantity) - SUM(T3.Quantity)) * SUM(T2.U_SCM_Weight) As 'Weight', (SUM(T1.Quantity) - SUM(T3.Quantity)) * SUM(T2.U_SMC_Volume) As 'Volume', (SUM(T1.Quantity) - SUM(T3.Quantity)) * SUM(T2.U_SMC_Seeds) As 'Bag', (SUM(T1.Quantity) - SUM(T3.Quantity)) * SUM(T2.U_SMC_Pack) As 'Pack', (SUM(T1.Quantity) - SUM(T3.Quantity)) * SUM(T2.U_SMC_Can) As 'Can' FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] LEFT OUTER JOIN RIN1 T3 ON T3.BaseEntry = T1.DocEntry AND T3.BaseType='13' and T3.BaseLine= T1.LineNum INNER JOIN OITB T4 ON T2.[ItmsGrpCod] = T4.[ItmsGrpCod] WHERE T0.[DocDate] BETWEEN [%0] AND [%1] AND T0.CANCELED = 'N' GROUP BY T1.[Dscription], T2.U_SMC_Sub_Category
Kind Regards,
Diego Lother