ULuFlanders ULuFlanders - 7 months ago 22
SQL Question

Calculating Column (weight/max(weight)

I have the following query:

select ema.es_symbol as symbol, ema.score as score, ema.weight as weight, rsi.relative_strength_index as relative_strength_index
from ema_score ema, relative_strength_index rsi inner join
(select rsi_symbol, max(rsi_date) as maxDate from relative_strength_index group by rsi_symbol) rsiDate
on rsi.rsi_symbol = rsiDate.rsi_symbol
and rsi.rsi_date = rsiDate.maxDate
where ema.es_symbol = rsi.rsi_symbol
and ema.score not in (0,1,10,11)
and rsi.relative_strength_index not in (0,100);


I am trying to add a calculated column like the one below as the final column:

ema.weight/max(ema.weight)


The result I want is each symbols weight divided by the max weight in the weight column. When I try it my way, I only receive 1 row of results. What exactly am I doing wrong here?

Answer

You have to use subquery with MAX as divisor, something like:

select ema.weight*1.0/(select max(weight) from #t ema2)
from #t ema