Calculate the average value without the highest or lowest value in a column. In statistics this is also called Trimmed mean which reduce the effect of outliers in a data set.
To solve this problem, we can use a subquery to exclude the Min and Max values.
SELECT AVG(Total) avg_order_value FROM invoice WHERE Total NOT IN ( (SELECT MAX(Total) FROM invoice), (SELECT MIN(Total) FROM invoice) );
First we find the minimum and maximum value of the total column inside the inner query. Then we use a WHERE Total NOT IN to exclude these min and max values in the outer query before calculating the averages.