CODEWITHSUNDEEP
hivehiveql
crusher
crusher

Reputation: 78

count distinct values from multiple column hive

I have a table like the following Id col1 col2 col3 1 Yes No No 2 No Yes No 3 Yes No No 4 No Yes No

I'm looking for a hive query to display results in the following format Value col1 col2 col3 Yes 2 2 0 No 2 2 4

Upvotes: 1

Views: 1172

Answers (1)

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

Reputation: 44921

select      val                                     as value
           ,count (case when pos = 0 then 1 end)    as col1 
           ,count (case when pos = 1 then 1 end)    as col2
           ,count (case when pos = 2 then 1 end)    as col3 

from        mytable
            lateral view posexplode(array(col1,col2,col3)) pe

group by   val

+-------+------+------+------+
| value | col1 | col2 | col3 |
+-------+------+------+------+
| No    |    2 |    2 |    4 |
| Yes   |    2 |    2 |    0 |
+-------+------+------+------+

Upvotes: 1

Related Questions