How do I exclude outliers from an aggregate query?

Question

I'm creating a report comparing total time and volume across units. Here a simplification of the query I'm using at the moment:

SELECT  m.Unit,
        COUNT(*) AS Count,
        SUM(m.TimeInMinutes) AS TotalTime
FROM    main_table m
WHERE   m.unit <> ''
        AND m.TimeInMinutes > 0
GROUP BY m.Unit
HAVING  COUNT(*) > 15

However, I have been told that I need to exclude cases where the row's time is in the highest or lowest 5% to try and get rid of a few wacky outliers. (As in, remove the rows before the aggregates are applied.)

How do I do that?

Answer 1

You can exclude the top and bottom x percentiles with NTILE

SELECT m.Unit,
        COUNT(*) AS Count,
        SUM(m.TimeInMinutes) AS TotalTime
FROM    
        (SELECT
             m.Unit,
             NTILE(20) OVER (ORDER BY m.TimeInMinutes) AS Buckets
         FROM
             main_table m
         WHERE
             m.unit <> '' AND m.TimeInMinutes > 0
        ) m
WHERE   
      Buckets BETWEEN 2 AND 19
GROUP BY m.Unit
HAVING  COUNT(*) > 15

Edit: this article has several techniques too

Answer 2

One way would be to exclude the outliers with a not in clause:

where  m.ID not in 
       (
       select  top 5 percent ID
       from    main_table 
       order by 
               TimeInMinutes desc
       )

And another not in clause for the bottom five percent.