CODEWITHSUNDEEP
hadoophivehiveql
Pragadeesh
Pragadeesh

Reputation: 113

Average of count in hive

I have an hive table

id name ....
 1   a
 2   a
 3   a
 4   b
 5   b
 6   b
 7   a

Need to find the average of number of occurences of name,

  name  avg
   a    0.57 (4/7)
   b    0.42 (3/7)

Please suggest me a query.

Upvotes: 1

Views: 1201

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

 select      name
            ,round (count(*) / sum(count(*)) over (),2) as avg
 from        mytable
 group by    name
 order by    name
 ;

+------+------+
| name | avg  |
+------+------+
| a    | 0.57 |
| b    | 0.43 |
+------+------+

Upvotes: 3

Related Questions