Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Average of Best 3

Posted on 2013-01-22
5
Medium Priority
?
234 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 143

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 143

Accepted Solution

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

618 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