I have received from a **select** query this kind of table (just a simple example):

```
D C Letter
20153 200 x
20154 300 x
20161 250 x
20162 180 x
20153 500 y
20154 380 y
20161 550 y
20162 170 y
20153 230 z
20154 700 z
20161 210 z
20162 185 z
```

So, the column D repeats after four entries. C is always different and Letter changes after four entries. In total, 20153 has 100.000, 20154 has 150.000, 20161 has 300.000 and 20162 has 250.000 entries. I would like to put these values to the corresponding numbers, calculate the percentage so it looks like this:

```
D C Letter Total Perc
20153 200 x 100.000 0.002
20154 300 x 150.000 0.002
20161 250 x 300.000 0.008
20162 180 x 250.000 ...
20153 500 y 100.000 ...
20154 380 y 150.000 ...
20161 550 y 300.000 ...
20162 170 y 250.000 ...
20153 230 z 100.000 ...
20154 700 z 150.000 ...
20161 210 z 300.000 ...
20162 185 z 250.000 ...
```

How can I do this? Provided solutions can also be written in R.

You have to define somewhere Your given vectors, for example in subquery `V`

. Subquery `X`

adds column `RN`

with rows partitioned by `letter`

and ordered by `D`

. Now we can `join`

these two queries and make division:

```
with
v as (select 1 rn, 100000 as total from dual union all
select 2 rn, 150000 as total from dual union all
select 3 rn, 300000 as total from dual union all
select 4 rn, 250000 as total from dual ),
x as (select t.*, row_number() over (partition by letter order by d) rn
from t)
select rn, d, c, letter, total, cast(c / total as number(8, 5)) percent
from x join v using (rn)
order by letter, d
```

Test data and output:

```
create table t (d number(6), c number(6), letter varchar2(2));
insert into t values (20153, 200, 'x');
insert into t values (20154, 300, 'x');
insert into t values (20161, 250, 'x');
insert into t values (20162, 180, 'x');
insert into t values (20153, 500, 'y');
insert into t values (20154, 380, 'y');
insert into t values (20161, 550, 'y');
insert into t values (20162, 170, 'y');
insert into t values (20153, 230, 'z');
insert into t values (20154, 700, 'z');
insert into t values (20161, 210, 'z');
insert into t values (20162, 185, 'z');
RN D C LETTER TOTAL PERCENT
----- ------- ------- ------ ---------- ----------
1 20153 200 x 100000 0,00200
2 20154 300 x 150000 0,00200
3 20161 250 x 300000 0,00083
4 20162 180 x 250000 0,00072
1 20153 500 y 100000 0,00500
2 20154 380 y 150000 0,00253
3 20161 550 y 300000 0,00183
4 20162 170 y 250000 0,00068
1 20153 230 z 100000 0,00230
2 20154 700 z 150000 0,00467
3 20161 210 z 300000 0,00070
4 20162 185 z 250000 0,00074
```