Solved

Average of Best 3

Posted on 2013-01-22
5
229 Views
Last Modified: 2013-01-22
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
Comment
Question by:SparkyP
  • 2
  • 2
5 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
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

by:Guy Hengel [angelIII / a3]
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

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
 

Author Comment

by:SparkyP
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

by:
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 

Open in new window

0
 

Author Closing Comment

by:SparkyP
ID: 38805361
Thanks so much, perfect.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question