Reputation: 45
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
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
Reputation: 1271151
Use window functions:
select bucket, value,
value * 1.0 / sum(value) over ()
from t;
Upvotes: 1