Calculating percentage in hive

hadoop, hiveql, percentage

Solution

Use the IF UDF to avoid the join

SELECT t1.colB,
   SUM( IF( colC == 'y', 1 , 0 ) )/ COUNT(*) * 100 as pct
FROM tbl t1
GROUP BY t1.colB;

Problem

I am having a little trouble getting the percentage. ``` colA | cloB | colC 4 | a | y 5 | b | y 7 | a | n 8 | a | y ------------------ Output: a 67% b 100% ``` I have to get the percentage of colC(all the 'y') for each letter in colB. I have been able to get both totals seperatly but can't seem to get the percentage to work. Gets the total 'y' for a,b(colB); ``` SELECT colB, COUNT(*) FROM tbl WHERE colC = '"y"' GROUP BY colB; Output: a 2 b 1 ``` Gets totals overall total for colB ``` SELECT COUNT(colC) FROM tbl WHERE colC = '"y"'; Output: 4 ``` Thanks in advance

Original source