CODEWITHSUNDEEP
hadoophivehiveql
Rahul Varma
Rahul Varma

Reputation: 550

how to extract month from date in hive and group it by month

I have hive table as below and now I need to group data by avg of each month

sample hive table:

 dat        amazon  tesla  infosys  facebook  apple 
 03/01/17  753.67   808.01 216.99   14.74     116.86
 04/01/17  757.18   807.77 226.99   15.13     118.69
 05/02/17  780.45   813.02 226.75   15.02     120.67
 06/05/17  795.99   825.21 229.01   14.82     123.41

sample output:

month  amazon  tesla  infosys  facebook  apple 
 1     782.2   843.23 548.87    24.42    143.35
 2     743.2   896.12 453.34    44.34    143.55

I need avg value of every month please help me

Upvotes: 1

Views: 1710

Answers (1)

leftjoin
leftjoin

Reputation: 38290

select cast(substr(dat, 4, 2) as int) as month,
       avg(amazon)                    as amazon,
       avg(tesla)                     as tesla,
       avg(infosys)                   as infosys,
       avg(facebook)                  as facebook,
       avg(apple)                     as apple
  from tablename
 group by cast(substr(dat, 4, 2) as int);

Upvotes: 1

Related Questions