HIVE/HiveQL Get Max count

Question

Sample Data

   DATE      WindDirection

   1/1/2000  SW
   1/2/2000  SW
   1/3/2000  SW
   1/4/2000  NW
   1/5/2000  NW

Question below

Every day is unqiue, and wind direction is not unique, So now we are trying to get the COUNT of the most COMMON wind direction

select w.wind_direction as most_common_wd
from (
       select wind_direction, count(*) as cnt
       from weather 
       group by wind_direction 
       order by cnt desc
) w
limit 1;

Currently this query works, however it outputs the count of ALL count, i am only interested in the count of each type of wind direction, it outputs south and outputs 170000 but the answer is only 10,000.

I am aware something is wrong the way count is being used, i think it have to specify an alias and do count by specfic wind_direction but i can't translate that into syntax

Please edit your question and include sample data and desired results. — Gordon Linoff, Apr 21 '16 at 15:56
I believe I had answered similar post yesterday. Adding `select distinct w.wind_direction as most_common_wd, cnt from (...` should work, you can get rid of `limit 1`. — gkc123, Apr 21 '16 at 16:32
Question title says you want counts by criteria but in the question body you mention you are interested in getting counts for each wind direction.Please correct the question and provide sample data,script you have tried and the desired output. — nobody, Apr 21 '16 at 16:52

nobody · Accepted Answer · 2016-04-21T17:32:55.663

2

Looks like you want the most common wind direction from your data

select wind_direction, count(*) as cnt
from weather 
group by wind_direction 
order by cnt desc
limit 1;

If there are multiple wind direction with the same max count then get the max count and use it in the having clause to get the most common wind direction

select wind_direction 
from weather 
group by wind_direction 
having count(*) = ( select max(c) from 
                    (
                       select wind_direction,count(*) c 
                       from weather 
                       group by wind_direction 
                    ) a
                  )

edited Apr 21 '16 at 17:32

answered Apr 21 '16 at 17:03

nobody

10,892
8
45
63

thank you and the first hive query u wrote is synonmous to the one you provided in this link in pig correct? http://stackoverflow.com/questions/36753093/trying-to-distinct-count-in-pig-latin-hadoop/36753897#36753897 – dedpo Apr 21 '16 at 17:32
Thank you! very clear, i am trying to learn pig and hive. Joins and sub queries are throwing me off – dedpo Apr 21 '16 at 17:42

HIVE/HiveQL Get Max count

1 Answers1