Hive Union Group By Error

Question

In the following query, I am trying to count up the distinct, and total occurences of the column "primary" and and summarise this for each column.

I have two source tables, which contain similar information. I want to union these to pull all the information together before counting this up.

However, using the logic below I get the following error. Can someone please advise where this is going wrong?

select COUNT(distinct primary), COUNT(primary), mycolumn 
from  (select primary, mycolumn from mytablea where mycolumn >= a and mycolumn <= b 
     union all 
     select primary, mycolumn from mytableb where mycolumn >= a and mycolumn <= b) 
group by mycolumn

FAILED: ParseException missing EOF at 'by' near 'group'

Thanks..

Answer 1

You have to give an alias to the subquery. Something like this should work:

select COUNT(distinct primary), COUNT(primary), mycolumn 
from  (select primary, mycolumn from mytablea where mycolumn >= a and mycolumn <= b 
     union all 
     select primary, mycolumn from mytableb where mycolumn >= a and mycolumn <= b) q1
group by mycolumn