CODEWITHSUNDEEP
hivehiveql
jumpman8947
jumpman8947

Reputation: 581

hive sql adding record count as column

I have records similar to the below

fruit      day
apple      1/1/1990   
apple      1/2/1990
apple      1/3/1990
plum       1/1/1990
orange     1/1/1990
orange     1/2/1990
orange     1/3/1990

I want to keep a running total for items for each day assuming item will increase by 1 every day. For example

fruit      day            count
apple      1/1/1990       1
apple      1/2/1990       2
apple      1/3/1990       3
plum       1/1/1990       1
orange     1/1/1990       1
orange     1/2/1990       2

Upvotes: 1

Views: 203

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can also use subquery:

select *,
       (select count(*) from table where fruit  = t.fruit and day <= t.day) count
from table t;

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

You could use windowed COUNT:

SELECT *, COUNT(*) OVER(PARTITION BY fruit ORDER BY day)
FROM tab;

DBFiddle Demo

Upvotes: 3

Related Questions