Column/Sum(Column) in amazon redshift

Question

I have some data like this:

ID   Var1     Var2   cvRate(Var1/Var2)
1     1250    85     14.70
2     15      1      15
3     496     12     41.33
4     8825    29     420.23
5     97      18     5.388

I would like to add a column that is essentially Var1/(sum(Var1)) like:

ID   Var1     Var2   cvRate(Var1/Var2) NewCol
1     1250    85     14.70             0.117
2     15      1      15                0.001
3     496     12     41.33             0.046
4     8825    29     420.23            0.826
5     97      18     5.388             0.009

I tried several variations of the query:

select ID, Var1, Var2, cvRate, Var1/sum(Var1) as NewCol from (<query that generates the first table>);


Error on group by caused by using sum

Where am I going wrong? Also, I don't have the luxury of creating and storing another table in redshift, I'll have to generate my table in-situ. Another issue I have is that the sub-query to generate the first table is fairly complex and takes a good amount of time. I might not be able to afford running the same query twice.

Gordon Linoff Gordon Linoff · Accepted Answer · 2015-01-14T11:57:17

You need a window function, not an aggregation function:

select ID, Var1, Var2, cvRate,
      Var1 / sum(Var1) over () as NewCol
from (<query that generates the first table>);

Column/Sum(Column) in amazon redshift

1 Answers