BigQuery - convert scientific notation to decimal format

Question

SELECT 
    SUM(totals.totalTransactionRevenue) *0.20 AS COGS
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` 
WHERE _TABLE_SUFFIX BETWEEN '20170701' AND '20170701';

Why does BigQuery automatically convert the created field 'Cost Of Goods Sold - COGS' to scientific notation? and how can i amend this query to pull the COGS as a decimal format value?

Thanks.

Answer 1

You can use the new numeric/decimal value to get the format you want:

SELECT cast(SUM(totals.totalTransactionRevenue) * 0.20 as numeric) AS COGS

It also works to cast as a string:

SELECT cast(SUM(totals.totalTransactionRevenue) * 0.20 as string) AS COGS