Take out the min and max values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to find a way to get the average without the highest value and
the lowest value in the calculation. Is there a "simple" way of doing this.
 
Try these two queries --

Min_Max_Audits --
SELECT Min(Audits.Audits) AS MinOfAudits, Max(Audits.Audits) AS MaxOfAudits
FROM Audits;

SELECT Avg(Audits.Audits) AS AvgOfAudits
FROM Audits, Min_Max_Audits
WHERE (((Audits.Audits)<>[MinOfAudits] And (Audits.Audits)<>[MaxOfAudits]));
 
You can do it either by using separate queries as Karl suggests or in a
single query with subqueries, e.g. to average customers' orders by amount,
excluding the highest and lowest per customer:

SELECT Customer, AVG(Amount)
FROM Customers INNER JOIN Orders AS O1
ON Customer.CustomerID = O1.OrderID
WHERE Amount <
(SELECT MAX(Amount)
FROM Orders AS O2
WHERE O2.CustomerID = O1.CustomerID)
AND Amount >
(SELECT MIN(Amount)
FROM Orders AS O3
WHERE O3.CustomerID = O1.CustomerID)
GROUP BY Customer;

However, if a customer had two orders of the same amount which happened to
be either their MAX or MIN amounts, then the average would exclude both of
those, which might or might not be what you want.

Ken Sheridan
Stafford, England
 
Back
Top