# How can I add a column with specific values ​​and calculate the percentage?

advertisements

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
```
```