Bigquery SUM(Float_Values) returns multiple decimal places and Scientific Notation

google-bigquery

Solution

BigQuery uses default formatting for floating point numbers, which means that depending on the size of the number, may use scientific notation. (See the %g format specifier here)

We tried switching this, but it turns out, it is hard to get a format that makes everyone happy. %f formatting always produces decimal format, but also pads decimals to a 6 digit precision, and drops decimals beyond a certain precision.

I've filed a bug to allow an arbitrary format string conversion function in BigQuery. It would let you run `SELECT FORMAT_STRING("%08d", SUM(Unit_Price)) FROM ...` in order to be able to control the exact format of the output.

Problem

I am trying to calculate Total Sales at a store. I have product Price in a column called UNIT_PRICE. All the prices have 2 decimal places example: 34.54 or 19.99 etc and they are imported as type:float in the schema. (UNIT_PRICE:float) When I perform the select Query: "SELECT CompanyName, SUM(Unit_Price) as sumValue" etc I get the following returned in the column, but only "sometimes". 2.697829165015719E7 It should be something like: 26978291.65 As I am piping this out into spreadsheets and then charting it I need it to be in the type float or at least represent a normal price format. I have tried the following but still having issues: - Source: Tried converting original data type to BigDecimal with only 2 decimal points in the source data and then exporting to the csv for import into bigquery but same result. - Bigquery: Tried converting to a string first and then to a float and then SUM but same result. "SELECT CompanyName, SUM(Float(String(Unit_Price))) as sumValue" Any ideas on how to deal with this? Thanks

Original source