Search code examples
sqlformattinggoogle-bigquerystring-formattingscientific-notation

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.

Solution

  • 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.