Skip to Content
Former Member
Jul 24, 2008 at 08:11 AM

total sales to date


Hi guys, I have the following query that i use to get total historical sales per item

SELECT T0.[ItemCode], T0.[Dscription], sum (T0.[Quantity]) as 'Total Units Sold', sum (T0.[LineTotal]) as 'Total Value', t0.currency FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode WHERE (t2.usertext not like N'000%' or t2.usertext is null) GROUP BY T0.[ItemCode], T0.[Dscription], T0.[Currency] ORDER BY T0.[ItemCode]

however, this gives me a break down of each price as well, so many items have two or three entries with the amounts sold at different prices. is it possible to add something to this query so that each item just appears once, with just a figure of total units sold? (sale price not important)

many thanks