HiveQL - Grouping Count

Question

I'm a newbie here and still facing a lot of problems in HiveQL, need to consult with you all. I have a table named Vote Table, and I'd like to count the "yes" vote for A,B,C,D (sorry I wasn't able to post the image, so I sent it as a link instead).

Vote_table

But here I'd like to only join the count of A1,A2,A3,A4 together; and for B,C will still be counted individually. The output I'm expecting would be

Result_table

What I've tried is

select
type,
count(
  case 
          when type = 'A1' and vote = 'yes' then 1
          when type = 'A2' and vote = 'yes' then 1
          when type = 'A3' and vote = 'yes' then 1
          when type = 'A4' and vote = 'yes' then 1
          else vote = 'yes' then 1
)
from vote_table
where …
group by type

I've also tried this way

if (type in ('A1', 'A2', 'A3', 'A4') and vote = 'yes' then count(*) else (if (vote = 'yes' then count(*)))) as cnt_yes

But both don't work. So, I'd like to consult it with the experts here, is there any better way in doing this? Thanks!

user_2700344 · Answer

Group by calculated type: case when type in ('A1', 'A2', 'A3', 'A4') then 'A' else type end

select
      case when type in ('A1', 'A2', 'A3', 'A4') then 'A' else type end as type,
      sum(case when vote = 'yes' then 1 else 0 end)                     as number_of_vote
 from vote_table
where ...
group by case when type in ('A1', 'A2', 'A3', 'A4') then 'A' else type end

HiveQL - Grouping Count

1 Answers