Segmenting data by percentage of a column total
You could calculate the percentage of the sum of a you've reached up till the current row, then segment that based on your thresholds.

with pct as (
  select a
  , (sum(a) over (order by a desc))/sum(a) over() pct
  from foo
)
select a
, pct
, sum(a) over (partition by
    case
      when pct &lt;= .6 then 'A'
      when pct &lt;= .9 then 'B'
      else                'C'
    end
    order by a desc) partial_sum
from pct
order by a desc;


Expanded version that actually shows the category (second subquery isn't necessary, just avoids repeating the partial sum expression):

with pct as (
  select a
  , (sum(a) over (order by a desc))/sum(a) over () pct
  from foo
), category as (
  select a
  , pct
  , case when pct &lt;= .6 then 'A'
         when pct &lt;= .9 then 'B'
         else                'C'
    end cat
  from pct
)
select a
, cat
, to_char(pct*100, '999.99')||'%' pct_of_total_a
, sum(a) over (partition by cat order by a desc) sum_by_category
from category
order by a desc;