Prou Prou Tyu Prou Prou Tyu - 11 months ago 31
SQL Question

How to calcule a ratio row by row in SQL server?

I have the following table :

Spect isin ticker
bonds 55 26
equity 36 89

And another one :

Spect totalnb
bonds 200
equity 200

I would like to calculate the ratio of Isin and ticker among the totalnb of spec like in SQL server:

Spect ratio_isin ratio_ticker
bonds 27 13
equity 18 39

I have already tried this solution but it works well only on 2 columns

Answer Source

You can use a simple join as follows

create table SpectDetail(Spect varchar(10), isin int, ticker int)
create table SpectTotal(Spect varchar(10), totalnb int)
insert into SpectDetail select 'bonds',55,26
insert into SpectDetail select 'equity',36,89
insert into SpectTotal select 'bonds',200
insert into SpectTotal select 'equity',200
    d.isin * 100 / t.totalnb ratio_isin, 
    d.ticker * 100/ t.totalnb ratio_ticker
from SpectTotal t 
inner join SpectDetail d on d.Spect = t.Spect

I hope it helps,