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

  • 17 March 2023
  • 1 reply


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?



Best answer by DataloopBill 20 March 2023, 20:54

View original

1 reply

Hey all!

I got a great answer from Looker Support (love these folks!) 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 ) ;;