sales per item report

[vasileiosfasolis]

hello experts

i have made the following query but i dont want to display double and triple times values per itemcode

i actually want to display the sum the quantity and the sum of the linetotal per item

SELECT 
--T0.CardName
-- T0.ShipToCode
-- T0.DocDate
 T1.ItemCode
, T1.Dscription
, sum(T1.Quantity)
, T1.LineTotal AS 'u0391u03BEu03AFu03B1 u03C0u03CEu03BBu03B7u03C3u03B7u03C2'
--, T2.CardCode 
FROM OINV T0  
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode 
INNER JOIN OSLP T3 ON T0.SLPCODE=T3.SLPCODE 
WHERE 
--(T0.CardName >= [%0] or [%0] = ' ') 
--and (T0.CardName<= [%1] or [%1] = ' ') 
--and (T0.ShipToCode >= [%2] or [%2] = ' ') 
--and (T0.ShipToCode <= [%3] or [%3] = ' ') 
(T2.CardCode = '80503')
--and (T2.CardCode = [%4] or [%4] = ' ') 
AND (T0.DOCDATE BETWEEN '2011-10-01' AND '2011-10-25') 
--AND (T0.DOCDATE BETWEEN '[%5]' AND '[%6]') 
--and (T0.CANCELED= 'N') 
--and ((T3.SLPNAME BETWEEN '[%7]'AND '[%8]') OR ('[%7]'=' ' AND  '[%8]'=' '))
group by T0.CardName,  T0.DocDate, T1.ItemCode, T1.Dscription, T1.Quantity, T1.LineTotal, T2.CardCode 
order by T1.ItemCode 

60344 test item1 1.000000 0.880000

60360 test item2 1.000000 1.500000

60360 test item2 1.000000 1.500000

60360 test item2 2.000000 3.010000

60361 test item3 2.000000 3.010000

do you have any idea?

thanks in advance for your time!

[Former Member]

Hi,

try this......

SELECT T1.ITEMCODE,T1.Dscription,sum(T1.Quantity) AS QTY,SUM(T1.LineTotal) AS TOTAL
FROM OINV T0
INNER JOIN INV1 T1 ON T1.DocEntry=T0.DOCENTRY 
WHERE  T1.DOCDATE>='[%0]' AND T1.DOCDATE<='[%1]'  AND T0.CardCode='[%2]'
GROUP BY T1.ItemCode,T1.Dscription
ORDER BY T1.ItemCode

Regards,

Priya

[vasileiosfasolis]

Hi,

No results comes up

[vasileiosfasolis]

i found it...

this is the solution

SELECT 
T1.ItemCode
, T1.Dscription
, sum(T1.Quantity)
, sum(T1.LineTotal) AS 'u0391u03BEu03AFu03B1 u03C0u03CEu03BBu03B7u03C3u03B7u03C2'
, t2.suppcatnum
FROM OINV T0  
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode 
WHERE 
(T2.CardCode = '80503')
AND (T0.DOCDATE BETWEEN '2011-10-01' AND '2011-10-25') 
group by  t1.itemcode, T1.Dscription, t2.suppcatnum
order by T1.ItemCode 

[Former Member]

Hi,

Try this.........

SELECT 
  T1.ItemCode,T1.Dscription,sum(T1.Quantity) QTY,sum(T1.LineTotal) TOTAL  
FROM OINV T0  
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode 
INNER JOIN OSLP T3 ON T0.SLPCODE=T3.SLPCODE 
WHERE 
 
(T0.DOCDATE BETWEEN '[%0]' AND '[%1]') AND T0.CardCode='[%2]' 
group by  T1.ItemCode, T1.Dscription

Regards,

Priya

[Former Member]

HI

try this:

SELECT 
--T0.CardName
-- T0.ShipToCode
-- T0.DocDate
 T1.ItemCode
, T1.Dscription
, sum(T1.Quantity)
, T1.LineTotal AS 'u0391u03BEu03AFu03B1 u03C0u03CEu03BBu03B7u03C3u03B7u03C2'
--, T2.CardCode 
FROM OINV T0  
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode 
INNER JOIN OSLP T3 ON T0.SLPCODE=T3.SLPCODE 
WHERE 
--(T0.CardName >= [%0] or [%0] = ' ') 
--and (T0.CardName<= [%1] or [%1] = ' ') 
--and (T0.ShipToCode >= [%2] or [%2] = ' ') 
--and (T0.ShipToCode <= [%3] or [%3] = ' ') 
(T2.CardCode = '80503')
--and (T2.CardCode = [%4] or [%4] = ' ') 
AND (T0.DOCDATE BETWEEN '2011-10-01' AND '2011-10-25') 
--AND (T0.DOCDATE BETWEEN '[%5]' AND '[%6]') 
--and (T0.CANCELED= 'N') 
--and ((T3.SLPNAME BETWEEN '[%7]'AND '[%8]') OR ('[%7]'=' ' AND  '[%8]'=' '))
group by T0.CardName,  T0.DocDate, T1.ItemCode, T1.Dscription, T1.LineTotal, T2.CardCode 
order by T1.ItemCode 

[vasileiosfasolis]

Hi,

it brings me the same results as before

i cannot see any changes in the code