Sql: add a column with integers in a loop for duplicates

I have a sql table like:

ID   Name     Balance
1    Peter    324.5
2    Michael  122.7
3    Peter    788.3
4    Mark     45.7
5    Ralph    333.5
6    Thomas   563.2
7    Ralph    9685.1
8    Peter    2444.5
9    Susi     35.2
10   Andrew   442.5
11   Susi     2424.8

Is it possible to write a while loop in sql, where you could add a whole new column with integer numbers (for example 1....3) for each duplicate names (3 times Peter, 2 times Susi, 2 times Ralph)? For the non duplicate names it should be a value of 0.

So the final table should look like this:

ID   Name     Balance   Value
1    Peter    324.5     1
2    Michael  122.7     0
3    Peter    788.3     1
4    Mark     45.7      0
5    Ralph    333.5     2
6    Thomas   563.2     0
7    Ralph    9685.1    2
8    Peter    2444.5    1
9    Susi     35.2      3
10   Andrew   442.5     0
11   Susi     2424.8    3

Solution

You wouldn't want to use a while loop for this. Just use window functions:

select t.*, count(*) over (partition by name) as cnt
from t;

This provides the total count for each name. If you want an incremental value, you can use row_number():

select t.*, row_number() over (partition by name order by id) as seqnum
from t;

This would enumerate the rows for each name, so every name would have a "1" value, some would have "2" and so on.