CODEWITHSUNDEEP
hivehiveqlhive-query
d-_-b
d-_-b

Reputation: 883

Hive nested SUM over partition - error Expression not in GROUP BY key

I'm trying to get cumulative sum in one query. It works fine in SQL, PRESTO etc. but not in HIVE, which throws error msg.

create table test(store varchar(10), item int, quantity int)
insert into test
select 'depot',101,1
union select 'depot',101,2
union select 'depot',101,5
union select 'depot',102,1
union select 'depot',102,3
store item revenue
depot 101 1
depot 101 2
depot 101 5
depot 102 1
depot 102 3 
select store, item,
sum(sum(revenue)) over (partition by store order by item)
from test
group by store, item

Expected output:

store item quantity
depot 101 8
depot 102 12

Error :

[Code: 40000, SQL State: 42000] Error while compiling statement: FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies. Underlying error: org.apache.hadoop.hive.ql.parse.SemanticException: Line 1:24 Expression not in GROUP BY key 'revenue'

Any suggestions ?

Upvotes: 0

Views: 381

Answers (1)

mck
mck

Reputation: 42352

You can separate the queries:

select store, item, sum(sum_revenue) over (partition by store order by item) as revenue
from (
    select store, item, sum(revenue) as sum_revenue
    from test
    group by store, item
) as t

Upvotes: 1

Related Questions