Sum In Query

[Former Member]

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,

[former_member185682]

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