I want to cost refrigerant based on the average of the best 3 prices from suppliers for each type of refrigerant.
I am using the following to get the best 3, but how would I then get the average per type?
SELECT TOP (100) PERCENT Type, Supplier, Cost, ID
WHERE ((SELECT COUNT(*) AS Expr1
FROM dbo.RefrigerantPricing AS f
WHERE (Type = dbo.RefrigerantPricing.Type) AND (Cost <= dbo.RefrigerantPricing.Cost)) <= 3)
ORDER BY Type