Hive sql: count and avg

Question

I recently tried to learn Hive and I have a problem with sql consult. I have a json file with some information. I want to get the average value for each register. Better in the example:

country  times
USA      1
USA      1
USA      1
ES       1
ES       1
ENG      1
FR       1

then with the following information:

select country, count(*) from data;

I get:

country   times
USA        3
ES         2
ENG        1 
FR         1

then I should get the following result:

country   avg
USA       0,42  (3/7)
ES        0,28  (2/7)
ENG       0,14  (1/7)
FR        0,14  (1/7)

I do not know how I can get this from the first table.

I tried:

select t1.country, avg(t1.tm), 
from (
    select country,count(*)as tm from data where not country is null group by country
) t1
group by t1.country;

but my way out is wrong.

Thanks for the help! BR.

Answer 1

Divide the amount of each group by the total counter to get the result. Use Sub-Queryto find the total number of records in the table.

try it

select t1.country, count(*)/IFNULL((select cast(count(*) as float) from data),0)
from data
group by t1.country;