I am running the following query to create a report. I noticed that a 'Credit Memo' was included in my report. Because of that SUM(INV1.LineTotal) does not show the right total. Probably I may need other tables to join so I can add a condition to ignore 'Credit Memo' in the report.
SELECT distinct SUM(INV1.quantity) AS Quantity, SUM(INV1.LineTotal) AS Total
FROM OINV INNER JOIN INV1 ON INV1.DocEntry = OINV.DocEntry INNER JOIN OCRD ON OCRD.CardCode = OINV.CardCode INNER JOIN OITM ON OITM.ItemCode = INV1.ItemCode INNER JOIN OITB ON OITB.ItmsGrpCod = OITM.ItmsGrpCod
WHERE INV1.TargetType <> '14' AND OITB.ItmsGrpNam = 'Sys 2'
Thanks in advance.