CODEWITHSUNDEEP
hive
user2672739
user2672739

Reputation: 143

How to ignore null values in sum in hive

I am having a table in hive with below values

ID  value
1
1

ID  value
1
1   2

while doing sum i need the output as 

select id,sum(val) from table group by id;

first required output

id sum
1

Second table Output

id sum
1  2

Upvotes: 1

Views: 7849

Answers (2)

Indrajit Swain
Indrajit Swain

Reputation: 1483

In math default 2+0=2 so its anyway going to work .Don't worry about this hivewill be default will take care this.

hive> create table first (Id int,value int);
OK
Time taken: 3.895 seconds
hive> select * from first;
OK
1       2
1       NULL
hive> select id, sum(value) as sum from first group by id;
Total MapReduce CPU Time Spent: 4 seconds 610 msec
OK
1       2
Time taken: 83.483 seconds, Fetched: 1 row(s)

Upvotes: 1

leftjoin
leftjoin

Reputation: 38335

If you need to filter out rows with null sum, use having:

select id, sum(value) from table group by id having sum(value) is not null;

Upvotes: 0

Related Questions