Average Sale Price by Item

[Former Member]

Dear Experts,

How to get Average Sale Price by Item.

Example :

Parameter : Item Code

S. No         Invoice No                         Invoice Date           Qty                Unit price                Amount            

1                   10                                    01/04/2015            1                     1000                         1000              

2                    100                                  31/08/2015           5                      2000                         10000 

Avg Cost : 11000/6= 1833.33

How to derive??

Please help me.

[frank_wang6 · Accepted solution]

SELECT T1.ItemCode, SUM(T1.Quantity*T1.Price)/SUM(T1.Quantity) AvgSellPrice FROM INV1 T1

GROUP BY T1.ItemCode

[kothandaraman_nagarajan]

Hi,

Try this query:

Select  S.I as Item,S.N as 'Inv No', S.D as 'INV Date' , S.Q as Qty, S.P as Price, S.T as Amount , Sum(T)/Sum(S.Q) as Avg

from(

SELECT t1.itemcode as I,T0.[DocNum] as N, T0.[DocDate] as D , T1.[Quantity] as Q, T1.[Price] as P, t1.linetotal as T FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T1.[ItemCode]  = [%0]) S group by S.N , S.D  , S.Q , S.P , S.T,S.I

But this query will give avg per line not overall avg for selected item. It is possible to get by crystal report not by query.

Thank you.

[Former Member]

Hai,

Thanks