"bad double value" in Google BigQuery

Question

I'm working in Google BigQuery (not using LegacySQL), and I'm currently trying to cast() a string as a float64. Each time I get the error "Bad double value". I've also tried safe_cast() but it completely eliminates some of my id's (Ex: if one customer repeats 3 times for 3 different dates, and only has 'null' for a single "Height" entry, that customer is completely eliminated after I do safe_cast(), not just the row that had the 'null' value). I don't have any weird string value in my data, just whole or rational numbers or null entries.

Here's my current code:

select id, date,        
       cast(height as float64) as height,    
       cast(weight as float64) as weight   
from (select id, date, max(height) as height, max(weight) as weight   
      from table    
      group by 1,2
     )
group by 1, 2

Answer 1

Of course safe_cast() returns NULL values. That is because you have inappropriate values in the data.

You can find these by doing:

select height, weight
from table
where safe_cast(height) is null or safe_cast(weight) is null;

Once you understand what the values are, fix the values or adjust the logic of the query.

If you just want the max of values are are properly numeric, then cast before the aggregation:

select id, date,
       max(safe_cast(height as float64)) as height,
       max(safe_cast(weight as float64)) as weight   
from table    
group by 1, 2;

A subquery doesn't seem necessary or desirable for your query.