CODEWITHSUNDEEP
sqlderived-column
noob_python
noob_python

Reputation: 45

calculate a derived column in sql

I have a following query which produces table below:-

select * from temp_3

bucket  values
OTIF+2  319
OTIF    24987              
null    1347               
OTIF+>2 515
OTIF+1  552

Now I need the values to be in percentage of the total sum of values for which I wrote following query :-

select sum(values) as total into temp_1 from temp_3
select bucket, (values/(select total from temp_1)) as score from temp_3

But that led to following result :-

bucket  values
OTIF+2  0
OTIF    0              
null    0               
OTIF+>2 0
OTIF+1  0

can anyone give me an idea as to how we can convert that efficiently into percentage form ? and where I am going wrong ?

Upvotes: 0

Views: 66

Answers (2)

Suchitra
Suchitra

Reputation: 101

select sum(values) as total into temp_1 from temp_3
select bucket, (values * 100) /total as score from temp_3, temp_1

or just

select bucket, (values * 100) /sum(values) as score from temp_3

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Use window functions:

select bucket, value,
       value * 1.0 / sum(value) over ()
from t;

Upvotes: 1

Related Questions