sql - Grouping of top 80% categories -

i need sql query grouping category present quantities of groups in total contain @ least 80% of categories, other rare categories (containing 20% of total) should represented "other".

so result of such query grouping apples category color should this:

red    1118 44% ) yellow  711 28% > @ least 80% green   229  9% ) other   482 19% 

how that?

i combination of aggregation , analytic functions. colors put in "other" category when cumulative sum of rarest under 20%:

select (case when cumcntdesc < totalcnt * 0.2 'other'              else color         end) color, sum(cnt) cnt (select color, count(*) cnt,              sum(count(*)) on (order count(*) asc) cumcntdesc,              sum(count(*)) on () totalcnt       t       group color      ) t group (case when cumcntdesc < totalcnt * 0.2 'other'                else color           end) 

here sql fiddle.