'Convert scientific notation to float after sum() in Bigquery

I have revenue float values as strings. After performing a simple query like:

SELECT
    EXTRACT(date FROM time) date,
    SUM(CAST(revenue AS FLOAT64)) number
FROM
    `mytable`
GROUP BY date
ORDER BY date DESC

I got this:

2019-03-11    3.2172407478999996E8
2019-03-10    2.3065347E7

And so on.

The question is - how to convert these values to float? I know there are questions of the same kind posted already on stack, but none of them brought me to a proper solution.

sqlformattinggoogle-bigquerystring-formattingscientific-notation


Solution 1:[1]

The value is already a float, just FORMAT it:

SELECT FORMAT('%.2f', 3.2172407478999996E8)
-- 321724074.79

PS: do not convert to float if revenue is some monetary/financial calculation; use decimal datatype instead.

Solution 2:[2]

I think if you cast to a string, you will get the value:

CAST(SUM(revenue) AS string) as number

Or numeric:

CAST(SUM(revenue) AS numeric) as number

Solution 3:[3]

what about INTEGER casting

SELECT
    EXTRACT(date FROM time) date,
    SUM(CAST(revenue AS INTEGER)) number
FROM
    `mytable`
GROUP BY date
ORDER BY date DESC

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Gordon Linoff
Solution 3 Zaynul Abadin Tuhin

Related Questions