How do I sum values that start as strings and have decimal points

Hello!

I have a BQ table that contains values like 10.0055858585 and 0.069872323 in a column that is a STRING type.

I converted the STRING to a NUMBER and cast it as an INTEGER, but am getting an error when I try to SUM these values - which seems right due to the fact that they are INTEGERS which can’t, by definition, have decimals.  

I’ve searched for an hour or more now and can’t seem to find out how to do this without causing other errors.

Anybody have any ideas?

Thanks!

Solved Solved
0 1 1,004
1 ACCEPTED SOLUTION

Hey all!

I got a great answer from Looker Support (love these folks!)

...in the first option we can also add another function such as trunc or round depending on how we want this data to be processed.

Code for the examples:
sql: CAST(ROUND(CAST(${string_number} AS FLOAT64)) AS INT64 ) ;;
sql: CAST(CAST(${string_number} AS FLOAT64) AS INT64 ) ;;

View solution in original post

1 REPLY 1

Hey all!

I got a great answer from Looker Support (love these folks!)

...in the first option we can also add another function such as trunc or round depending on how we want this data to be processed.

Code for the examples:
sql: CAST(ROUND(CAST(${string_number} AS FLOAT64)) AS INT64 ) ;;
sql: CAST(CAST(${string_number} AS FLOAT64) AS INT64 ) ;;

Top Labels in this Space
Top Solution Authors