Skip to Content
0

Query - Products not grouping

Dec 09, 2016 at 04:18 PM

38

avatar image

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]

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
DIEGO LOTHER Dec 09, 2016 at 05:04 PM
1

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

Share
10 |10000 characters needed characters left characters exceeded
Roy Bright Dec 12, 2016 at 09:24 AM
0

Thanks Diego - spot on as usual.

Regards

Roy

Share
10 |10000 characters needed characters left characters exceeded