Clever Geek Handbook

Hive Union Group by mistake

In the following query I am trying to count the individual and total occurrences of the "primary" column and sum this for each column.

I have two original tables containing similar information. I want to combine them so I can put all the information together before counting this.

However, using the logic below, I am getting 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 in 'by' near 'group'

Thank..

+3


source to share


1 answer


You must provide an alias for 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
+14


source






More articles:


All Articles