• Status: Solved
• Priority: Medium
• Security: Public
• Views: 238

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
0
SparkyP
• 2
• 2
1 Solution

Commented:
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]
0

Billing EngineerCommented:
this should do, first step:
``````SELECT  Type, Supplier, Cost, ID, row_number() over (partition by Type order by cost asc) rn
FROM         dbo.RefrigerantPricing
``````

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
``````

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

Author Commented:
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.
0

Billing EngineerCommented:
well ....

``````select Type, AVG(cost)
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
group by Type
``````
0

Author Commented:
Thanks so much, perfect.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Featured Post

• 2
• 2
Tackle projects and never again get stuck behind a technical roadblock.