Solved

# Average of Best 3

Posted on 2013-01-22
228 Views
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
Question by:SparkyP
• 2
• 2

LVL 39

Expert Comment

ID: 38805126
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

LVL 142

Expert Comment

ID: 38805134
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 Comment

ID: 38805191
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

LVL 142

Accepted Solution

Guy Hengel [angelIII / a3] earned 250 total points
ID: 38805227
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 Closing Comment

ID: 38805361
Thanks so much, perfect.
0

## Featured Post

### Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.