asked on

Average of Best 3

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
FROM dbo.RefrigerantPricing
WHERE ((SELECT COUNT(*) AS Expr1
FROM dbo.RefrigerantPricing AS f
WHERE (Type = dbo.RefrigerantPricing.Type) AND (Cost <= dbo.RefrigerantPricing.Cost)) <= 3)
ORDER BY Type

Pratima

Select X.[PERCENT Type] , AVG(Cost)
From(

SELECT TOP (100) [PERCENT Type], Supplier, Cost, ID
FROM dbo.RefrigerantPricing
WHERE ((SELECT COUNT(*) AS Expr1
FROM dbo.RefrigerantPricing AS f
WHERE (Type = dbo.RefrigerantPricing.Type) AND (Cost <= dbo.RefrigerantPricing.Cost)) <= 3) ) X
Group by X.[PERCENT Type]

Guy Hengel [angelIII / a3]

this should do, first step:

SELECT  Type, Supplier, Cost, ID, row_number() over (partition by Type order by cost asc) rn
FROM         dbo.RefrigerantPricing

Open in new window

and as you want only the top 3 per type:

select * from (
SELECT  Type, Supplier, Cost, ID, row_number() over (partition by Type order by cost asc) rn
FROM         dbo.RefrigerantPricing
) sq
where sq.rn <=3

Open in new window

from there, I think. you can do it yourself?
https://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html

SparkyP

ASKER

Thanks both for your rapid response. I think you are giving me nore credit than I deserve, unsure how to fully compile to get the results.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SparkyP

ASKER

Thanks so much, perfect.