Concat and then group by in Hive

Question

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

select dept, class, item, concat(dept, '-', class, '-', item) as item_no, sum(sales) from sales_table group by dept, class, item, item_no; — Reema, Apr 20 '20 at 11:53
Please refer https://stackoverflow.com/questions/29443253/hive-more-clean-way-to-select-as-and-group-by if it helps. — yammanuruarun, Apr 22 '20 at 14:49

score 2 · Accepted Answer · answered Apr 20 '20 at 11:48

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 ;

Concat and then group by in Hive

1 Answers1