ULuFlanders - 8 months ago 28

SQL Question

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