Skip to Content
avatar image
Former Member

Sum In Query

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,

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jan 09, 2017 at 01:26 AM

    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

    Add comment
    10|10000 characters needed characters exceeded