calculations in BigQuery returns E-4 SQL

Question

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:

The last number 930000 should have returned 0.00093

That's what I thought as well, but then my SUM in the separate query gives me the wrong result. — Chique_Code, Oct 22 '20 at 18:10

score 2 · Accepted Answer · answered Oct 22 '20 at 18:08

2

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

answered Oct 22 '20 at 18:08

Mikhail Berlyant

1

Perfect! That works, I have accepted the answer and upvoted it. – Chique_Code Oct 22 '20 at 18:19

1 Answers1