Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
331 views
in Technique[技术] by (71.8m points)

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

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...