Query Help [group by]

27 Views

we use a query for sales analyze , its is for document wise and item wise

now we want to group the results of query

this the query,

SELECT T1.[DocNum]as Document,T1.[DocDate]as Doc_Date, T0.[ItemCode], T0.[Dscription], T0.[unitMsr],T0.[Quantity], T0.[PriceBefDi], T0.[DiscPrcnt]as Disc_Per, T0.[Price]as Sales_Price, T0.[GrossBuyPr]as Item_Cost, (T0.[Price]- T0.[GrossBuyPr])as GP_SalesPrice, ((T0.[Price]- T0.[GrossBuyPr])/ T0.[Price]*100)as GP_PerSP, (T0.[PriceBefDi]/ T0.[GrossBuyPr])as Cos_Ratio, (( T0.[PriceBefDi]- T0.[GrossBuyPr])/ T0.[PriceBefDi] 100)as GP_PerUP,(T0.[Quantity](T0.[Price]- T0.[GrossBuyPr]))as Line_Total_GP FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry INNER JOIN NNM1 T2 ON T1.Series = T2.Series WHERE T2.[SeriesName] =[%0] AND T1.[DocDate] >=[%1] AND T1.[DocDate] <=[%2]

we want to group it like this

group start with T0.[ItemCode]

T0.[Quantity]- sum of quantity

T0.[GrossBuyPr]as Item_Cost - average amount

(T0.[Price]- T0.[GrossBuyPr])as GP_SalesPrice - average amount

((T0.[Price]- T0.[GrossBuyPr])/ T0.[Price]*100)as GP_PerSP - average amount

(T0.[PriceBefDi]/ T0.[GrossBuyPr])as Cos_Ratio - average amount

(( T0.[PriceBefDi]- T0.[GrossBuyPr])/ T0.[PriceBefDi] *100)as GP_PerUP - average amount

other value fields by sum of the filed

can any one help me?

saman