0

I have a table that has 'NaN' in a field that is a double. I simply want to count how many items are 'NaN':

Select count(*) from table
where col = 'NaN'

AnalysisException: operands of type DOUBLE and STRING are not comparable: col = 'NaN'

Select count(*) from table
where col is null

Result = 0 (there are tons of NaN records in this column btw)

Select count(*) from table
where cast(col as string) = 'NaN'

Result = 0

How do I do this where it will actually count the NaN rows?

user3486773
  • 1,174
  • 3
  • 25
  • 50

3 Answers3

0

I would cast the NaNs into string and then do the comparison with 'nan'

Select count(*) from table
where cast(col as string) = 'nan'
Lucas
  • 1,833
  • 1
  • 18
  • 19
  • I was using 'NaN' which is how it appears in the data, but when cast as a string, it is all lowercase. that was my issue. Thank you. – user3486773 Jul 18 '19 at 15:51
0

You can use the is_nan function

select count(*) from table
where is_nan(col)
fc9.30
  • 2,293
  • 20
  • 19
0

Slightly different way to count..

select sum(if(is_nan(column_name), 1, 0)) as nan_count, count(column_name) AS total_count
from table_name;
Keith Sirmons
  • 8,271
  • 15
  • 52
  • 75