Hive - i would like to calculate percentage of column and based on the percentage i would like to load the data into another table

[#1]

Hive - i would like to calculate percentage of column and based on the percentage i would like to load the data into another table(if the percentage of n is less 20%) or else not to load

colA

y

y

y

n

------------------ Output: -- This is what i am expecting

y 80%

n 20%

[#2 · Accepted solution]

@PPR Reddy

Here goes the solution (You can do it in other ways if you choose to):

Column name in table a is c

hive> select * from a;

OK

y

y

y

n

hive>

Query:

hive> select c,per from(  > select x.c c,(x.cc/y.ct)*100 per,(z.cn/y.ct)*100 pern  from  > (select c, count(*) cc from a group by c) x, (select count(*)ct from a) y,  > (select c, count(*) cn from a where c='n' group by c) z) f  > where pern > 20;

Output:

OK

n 25.0

y 75.0

Thanks