Skip to Content

Query - Products not grouping

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]

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Dec 09, 2016 at 05:04 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 12, 2016 at 09:24 AM

    Thanks Diego - spot on as usual.

    Regards

    Roy

    Add comment
    10|10000 characters needed characters exceeded