Search code examples
sqlhivehiveqlhive-query

Divide each value of a column by the total count of records in a table

A query that is capable of dividing each value of a column by the total number of records in the table

I tried the following query

select ( (p.rank/count(*)) * 100 ) as rankratio from RankTable p;

I see an error and not able to execute the query. for example
total records is 5 so (1/5)*100 = 20

RankTable         
rank            rankratio        
1               20
2               40
3               60
4               80
5               100
Solution

  • use analytic count(*) over():

    select ( (s.rank/s.total_count) * 100 ) as rankratio 
from
(
select rank, count(*) over() as total_count
  from RankTable p
)s
order by s.rank;