CODEWITHSUNDEEP
hivehiveql
HHH
HHH

Reputation: 6465

how to do group by in HIVE

I'm writing a hive query in which I need to group by a few field however I need to select some other fields besides those exist in the group by statement. That is,

select A,B,C from table_name GROUP BY A,B

HIVE complains and says Invalid table alias or column reference C. It requires me to put C in the GROUP BY part but that changes my logic. How can I resolve this issue?

Upvotes: 2

Views: 30654

Answers (3)

user12255828
user12255828

Reputation: 1

You can try using cluster by instead of group by

select A,B,C from table_name CLUSTER BY A,B

Upvotes: 0

Vijiy
Vijiy

Reputation: 1197

You have to join after group by.

select T1.*, t2.c from (select a,b, count(*) as SomeAggFunc from table group by a,b) T1;
<join condition> table t2 on t1.a=t2.a and t1.b=t2.b

Upvotes: 1

Ronak Patel
Ronak Patel

Reputation: 3849

HIVE-Select-statement-and-group-by-clause - group by must be used with some aggregate function like count, sum etc.

so there must be one of the aggregate calculation on column C

example:

select A,B,count(C) as Total_C from table_name GROUP BY A,B;
select A,B,SUM(C) as Total_C from table_name GROUP BY A,B;

Upvotes: 4

Related Questions