CODEWITHSUNDEEP
hivenullsumhiveql
Inban
Inban

Reputation: 33

How to count records from multiple columns eliminating null values in hive table

I'm using the below command to find the sum of records from 8 columns but getting null in the O/P as shown below.

Command part 1

command part 2

Output

How can this be fixed?

Upvotes: 1

Views: 203

Answers (1)

leftjoin
leftjoin

Reputation: 38335

Yes, the thing is NULL + something results NULL. To solve this, wrap each sum() in the NVL(sum(),0), so if some particular sum() is NULL, it is converted to 0 and the whole total will be not null:

nvl(sum(case when col1='something' then 1 else 0 end),0)+ ...

Or always use else 0, like in the first expression (H).

Wrapping with NVL() will solve the problem even if column comes from the join and the rows are absent and sum is NULL.

Upvotes: 1

Related Questions