Search code examples
sqlmathgoogle-bigquerycalculated-columns

calculations in BigQuery returns E-4 SQL

I need to perform simple calculations in BigQuery and save the result in a new column. Below is my code:

SELECT id, 
CPM, 
(1 / 1000000000 * CPM) AS Revenue
FROM `big_query_table`

For some numbers, it does the calculations right, but for other numbers, it is not even a number that is being returned. Here is the output:

enter image description here

The last number 930000 should have returned 0.00093

Solution

  • use below instead (BigQuery Standard SQL)

    SELECT id, 
CPM, 
CAST(1 / 1000000000 * CPM as numeric) AS Revenue
FROM `big_query_table`

    this will output below

    enter image description here