Query - Products not grouping

[former_member239716]

Hi guys - the following query won't seem to group the products together into one single line - the math and totals behind everything seem to be correct but it is listing the products (seemingly) by individual orders and I am trying to have them listed as one line per item - prior to adding the currency conversions, the query worked perfectly - any help will be greatfully appreciated:

SELECT T2.[ItemCode]
, T2.[ItemName]
, SUM (T1.[Quantity]) AS 'Total Qty Taken'
, CASE
WHEN T3.[Currency] = 'EUR' THEN SUM (T1.[LineTotal]) * 1.19
WHEN T3.[Currency] = 'USD' THEN SUM (T1.[LineTotal]) * 1.26
WHEN T3.[Currency] = 'GBP' THEN SUM (T1.[LineTotal]) * 1
/* etc */
ELSE T1.[LineTotal]
END AS 'Total Sales'

FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode

WHERE T0.[CardCode] = [%0] AND T0.[DocDate] BETWEEN [%1] AND [%2] AND T1.[ItemCode] NOT Like '%%Carriage%%' AND T1.[ItemCode] NOT Like '%%SPOOKY%%' AND T1.[ItemCode] NOT Like '%%XMAS%%'

GROUP BY
T1.[LineTotal], T3.[Currency], T2.[ItemCode], T2.[ItemName]

[former_member185682 · Accepted solution]

Hi Roy,

This happens because you inserted the field LineTotal in your group by clause. What you need do is remove the field LineTotal of your clause "group by" and change the place of sum function in your select clause, you should envolve all your "case when" condition inside the sum function. Look this:

SELECT 
	T2.[ItemCode]
	, T2.[ItemName]
	, SUM (T1.[Quantity]) AS 'Total Qty Taken'
	, SUM(CASE 
		WHEN T3.[Currency] = 'EUR' THEN T1.[LineTotal] * 1.19
		WHEN T3.[Currency] = 'USD' THEN T1.[LineTotal] * 1.26
		WHEN T3.[Currency] = 'GBP' THEN T1.[LineTotal] * 1
		/* etc */
	ELSE 
		T1.[LineTotal]
	END) AS 'Total Sales'
FROM 
	OINV T0 
	INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
	INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
	INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode
WHERE 
	T0.[CardCode] = [%0] 
	AND T0.[DocDate] BETWEEN [%1] AND [%2] 
	AND T1.[ItemCode] NOT Like '%%Carriage%%' 
	AND T1.[ItemCode] NOT Like '%%SPOOKY%%' 
	AND T1.[ItemCode] NOT Like '%%XMAS%%'
GROUP BY
	T3.[Currency], T2.[ItemCode], T2.[ItemName]

Kind Regards,

Diego Lother

[former_member239716]

Thanks Diego - spot on as usual.

Regards

Roy