sql - Find column Value by dividing with sum of a column

Question

I have a table with the following columns in a Netezza Database

Id  field1  field2      
 1   0.9    sum(field1)/0.9
 2   1.7    sum(field1)/1.7
 3   6.9    sum(field1)/6.9
 4   0.4    sum(field1)/0.4
 5   0.2    sum(field1)/0.2
 6   2.8    sum(field1)/2.8
 7   7.0    sum(field1)/7.0

The field2 column is to be generated and will hold values as shown in the table.

For this example the sum of field1 is 19.9.

Also the field1 values will change frequently.

Please help with a query that will help calculate the field2 value also the values are in decimals so will want the precise sum.

Thanks

Answer 1

You can use OUTER APPLY:

select t1.id,
  t1.field1,
  (t2.field1 / t1.field1) field2
from table1 t1
outer apply
(
  select sum(field1) field1
  from table1
) t2

see SQL Fiddle with Demo

If you don't have access to outer apply then you can use a cartesian result query:

select t1.id,
  t1.field1,
  (t2.field1 / t1.field1) field2
from table1 t1, 
(
  select sum(field1) field1
  from table1
) t2

See SQL Fiddle with Demo