CODEWITHSUNDEEP
hive
HHH
HHH

Reputation: 6485

how to replace NULL values with 0 when calculating the AVG in HIVE

I'm calculating the AVG for a column in HIVE (using a Windowing function). The query looks like this

SELECT AVG(price) (PARTITION BY cust_id ORDER BY mnth DESC ROWS BETWEEN 1 FOLLOWING AND 12 FOLLOWING)

The price column has some NULL values and the AVG function do not consider them in the calculation. However I need want the NULL values to be dealt with as 0. I use NVL as follows

SELECT AVG(NVL(price)) (PARTITION BY cust_id ORDER BY mnth DESC ROWS BETWEEN 1 FOLLOWING AND 12 FOLLOWING)

However this doesn't work. How can I deal with this problem?

Upvotes: 1

Views: 2351

Answers (1)

Piotr Uchman
Piotr Uchman

Reputation: 570

SELECT AVG(NVL(price, 0))

You forgot about second parameter (default_value)

Upvotes: 1

Related Questions