Sql assign unique key to groups having particular pattern

Hi I was trying to group data based on a particular pattern.

I have a table with two column as below,

Name  rollingsum
 A        5
 A        10
 A        0
 A        5
 A        0
 B        6
 B        0

I need to generate a key column that increment only after rollingsum equals 0 is encountered.As given below

Name   rollingsum   key
  A       5          1
  A       10         1
  A        0         1
  A        5         2
  A        0         2
  B        6         3
  B        0         3

I am using postgres, I tried to increment variable in case statement as below

Declare a int;
a:=1;

........etc 
Case when rolling sum =0 then a:=a+1 else a end as key

But I am getting an error near :

Thanks in advance for all help

Answer

You need an ordering columns because the results depend on the ordering of the rows — and SQL tables represent unordered sets.

Then do a cumulative sum of the 0 counts from the end of the data. That is in reverse order, so subtract that from the total:

select t.*,
       (1 + sum( (rolling_sum = 0)::int ) over () -
        sum( (rolling_sum = 0)::int ) over (order by ordercol desc)
       ) as key
from t;