sql - Grouping of top 80% categories

Question

I need SQL query for grouping by some category which would present quantities of only those groups which in total contain at least 80% of all categories, other rare categories (containing up to 20% of total) should be represented like "other".

So the result of such a query for grouping apples by category color should look like this:

RED    1118 44% )
YELLOW  711 28% > at least 80%
GREEN   229  9% )
other   482 19%

How to do that?

Answer 1

I would do this with a combination of aggregation and analytic functions. The colors are put in the "other" category when the cumulative sum of the rarest is under 20%:

select (case when cumcntdesc < totalcnt * 0.2 then 'other'
             else color
        end) as color, sum(cnt) as cnt
from (select color, count(*) as cnt,
             sum(count(*)) over (order by count(*) asc) as cumcntdesc,
             sum(count(*)) over () as totalcnt
      from t
      group by color
     ) t
group by (case when cumcntdesc < totalcnt * 0.2 then 'other'
               else color
          end)

Here is a SQL Fiddle.