MySQL: find mean after removing top and bottom nth% of data
I have a table with 3 columns: list_num, sales_price and days_on_market
I want to run a single query that will print a single number for the average of the column "days_on_market" with the top 5% and bottom 5% of the rows removed in the "sales_price" order.
I found an example that I think will help here, but the answer is too complex for me to understand (I just started learning mySQL last week). Here's an example here .
Here is my code:
SELECT round(avg(days_on_market),0) as "90% Sell In"
FROM sold_q3_2016
WHERE list_num NOT IN (
SELECT list_num FROM sold_q3_2016 ORDER BY sales_price LIMIT (count(list_num)*0.05)
) OR list_num NOT IN (
SELECT list_num FROM sold_q3_2016 ORDER BY sales_price desc LIMIT (count(list_num)*0.05)
)
;
Now I can't even run this as I get a syntax error around the count function inside the LIMIT clause, but I wanted to leave it here to show my thought process. Any ideas?
+3
source to share
1 answer
Try with this:
SELECT AVG(days_on_market)
FROM (
SELECT
l.*
, @rownumber := @rownumber + 1 AS rownumber
FROM list_num l
, (SELECT @rownumber := 0) var_init_subquery
ORDER BY sales_price DESC
) subquery_alias
WHERE rownumber >= @rownumber * 0.05
AND rownumber <= @rownumber * 0.95
- More information on custom variables
+3
source to share