mySQL: Finding average after removing top and bottom nth% of data

Question

I have a table with 3 columns: list_num, sales_price, and days_on_market

I want to run one query that will output a single number for the average of column 'days_on_market' with the top 5% and bottom 5% of rows removed when ordered by 'sales_price'.

I have found an example that I think would help here but the answer is too complicated for me to understand (I just started learning mySQL last week). See this 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 the syntax error around the count function inside of the LIMIT clause but I wanted to leave it here to show my thought process. Any ideas?

score 3 · Accepted Answer · answered Jun 02 '17 at 10:35

3

Have a 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 user-defined variables

answered Jun 02 '17 at 10:35

fancyPants

50,732
33
89
96

Nice, I was working on a different approach using `LIMIT` but that seems not to accept the use of variables ... which meant converting to prepared statements (due to an apparent bug see https://stackoverflow.com/questions/245180/variable-limit-clause-in-mysql). – Steve Lovell Jun 02 '17 at 10:53

mySQL: Finding average after removing top and bottom nth% of data

1 Answers1