hive select count based on average of count

Question

Hi I am trying to work on finding the count which is higher than average using the below hive statement

Select x, Count(x) as y from data  

group by x

Having Count(x) >= (select Avg(z.count1) as aveg 
                    from (select x, Count(x) as count1 from data group by x ) z) ;

I am receiving error as ParseException line 1:87 cannot recognize input near 'Select' 'Avg' '(' in expression specification

Answer 1

select      x
           ,cnt_x

from       (select      x
                       ,count(x)                as cnt_x
                       ,avg (count(x)) over ()  as avg_cnt_x

            from        data  

            group by    x
            ) t

where       cnt_x >= avg_cnt_x