How do I exclude outliers from an aggregate query?

Posted by Margaret on Stack Overflow See other posts from Stack Overflow or by Margaret
Published on 2011-01-17T20:23:16Z Indexed on 2011/01/17 21:53 UTC
Read the original article Hit count: 106

Filed under:

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?

© Stack Overflow or respective owner

Related posts about sql-server