CODEWITHSUNDEEP
hadoophive
user3252809
user3252809

Reputation: 136

Aggregated column use in Hive Query

My hive table (tab1) structure:

people_id,time_spent,group_type
1,234,a
2,540,b
1,332,a
2,112,b

Below is the query i am trying to execute but getting error ("Not yet supported place for UDAF 'sum'"):

select people_id, sum(case when group_type='a' then time_spent else 0 end) as a_time, sum(pow(a_time,2)) as s_sq_a_time,sum(case when group_type='b' then time_spent else 0 end) as b_time, sum(pow(b_time,2)) as s_sq_b_time from tab1 group by people_id;

Is it possible to refer aggregated column from same select statement in Hive? I have also referred below link but it didnt work: http://grokbase.com/t/hive/user/095tpdkrgz/built-in-aggregate-function-standard-deviation#

Upvotes: 1

Views: 6301

Answers (2)

Kaushalya
Kaushalya

Reputation: 1029

Set an alias for the table name and use the table alias when accessing the columns.

E.g.

select startstation, count(tripid) as a
from 201508_trip_data as t
group by t.startstation

Note 't' is the alias for the table and I've used t.startstation to access the

Upvotes: 1

FuzzyTree
FuzzyTree

Reputation: 32402

You'll have to use a derived table to refer to a_time and b_time

select a_time, b_time,
pow(a_time,2) as s_sq_a_time,
pow(b_time,2) as s_sq_b_time
from (
    select people_id,
    sum(case when group_type='a' then time_spent else 0 end) as a_time,
    sum(case when group_type='b' then time_spent else 0 end) as b_time 
    from tab1 group by people_id
) t1

Upvotes: 0

Related Questions