Grouping Of Top 80% Categories

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 (con

Solution 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 (casewhen cumcntdesc < totalcnt *0.2then'other'else color
        end) as color, sum(cnt) as cnt
from (select color, count(*) as cnt,
             sum(count(*)) over (orderbycount(*) asc) as cumcntdesc,
             sum(count(*)) over () as totalcnt
      from t
      groupby color
     ) t
groupby (casewhen cumcntdesc < totalcnt *0.2then'other'else color
          end)

Here is a SQL Fiddle.