4

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

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

then with next consult:

select country, count(*) from data;

I obtain:

country   times
USA        3
ES         2
ENG        1 
FR         1

then i should get next out:

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

I don't know how i can obtain this out 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 out is wrong.

Thanks for help!! BR.

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
Bob RO
  • 41
  • 1

1 Answers1

0

Divide the each group count by total count to get the result. Use Sub-Query to find the total number of records in your table

Try this

select t1.country, count(*)/IFNULL((select cast(count(*) as float) from data),0)
from data
group by t1.country;
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172