CODEWITHSUNDEEP
sqlhadoophivehiveql
Reema
Reema

Reputation: 33

Concat and then group by in Hive

I have 3 columns in a table as given below:

|---------------------|------------------|-------------|
|      dept           |     class        |    item     |
|---------------------|------------------|-------------|
|          234        |         34       |      6783   |
|---------------------|------------------|-------------|
|          784        |         78       |      2346   |
|---------------------|------------------|-------------|

while I'm concatenating 3 columns and creating a column as 'item_no' (value 234-34-6783), it throws an error when I'm using the new column item_no in group by function - 'Invalid table alias or column reference' Could someone help me with this?

select dept, class, item, concat(dept, '-', class, '-', item) as item_no, sum(sales)
from sales_table
group by dept, class, item, item_no;

column data types are smallint

Upvotes: 1

Views: 753

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Here are two methods:

select concat(dept, '-', class, '-', item) as item_no, count(*)
from t
group by concat(dept, '-', class, '-', item) ;

Or:

select concat(dept, '-', class, '-', item) as item_no, count(*)
from t
group by dept, class, item ;

That said, I thought Hive supported aliases in group by, so this should also work:

select concat(dept, '-', class, '-', item) as item_no, count(*)
from t
group by item_no ;

This would not work if item_no were a column in the table, though. And positional notation also works:

select concat(dept, '-', class, '-', item) as item_no, count(*)
from t
group by 1 ;

Upvotes: 2

Related Questions