CODEWITHSUNDEEP
sqlhive
Dave Diggler
Dave Diggler

Reputation: 33

Include null values in count in Hive

I have a dataset with null values in but when I run the count() command, the null values are returned as 0:

select year, income_band,
    count(income_band)
from income_dataset
where year = "2017"
group by income_band, year

How do I run the count so that the count of the null values are returned?

Thanks!

Upvotes: 0

Views: 31

Answers (2)

Bart McEndree
Bart McEndree

Reputation: 3293

The function nvl might fix your issue

Count(nvl(income_band, 1))

https://cwiki.apache.org/confluence/pages/viewpage.action?pageId=27362046#LanguageManualUDF-ConditionalFunctions

Upvotes: 0

Sergey
Sergey

Reputation: 5225

WITH DATASET(YEARR,INCOME_BAND)AS
(
   SELECT 2017,1 UNION ALL
   SELECT 2017,1 UNION ALL
   SELECT 2017,2 UNION ALL
   SELECT 2017,NULL UNION ALL
   SELECT 2017,NULL UNION ALL
   SELECT 2017,NULL
)
SELECT T.YEARR,T.INCOME_BAND,
 COUNT(CASE WHEN T.INCOME_BAND IS NULL THEN 1 ELSE   T.INCOME_BAND END)CNTT
FROM DATASET AS T
GROUP BY T.YEARR,T.INCOME_BAND

Please try conditional aggregation

Upvotes: -1

Related Questions