[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

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
Asked:
SparkyP
  • 2
  • 2
1 Solution
 
Pratima PharandeCommented:
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
 
Guy Hengel [angelIII / a3]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

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?
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0
 
SparkyPAuthor 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
 
Guy Hengel [angelIII / a3]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 

Open in new window

0
 
SparkyPAuthor Commented:
Thanks so much, perfect.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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