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

Question

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

Answer 1

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.

Answer 2

Do you see this in the BQ browser tool or only on your spreadsheet? BQ float is of size of 8 bytes, so it can hold numbers >9,000,000,000,000...

I find it that sometimes when Excel opens a flat file (csv) it converts it to the format you mentioned. To verify this is the case, try to open your csv with notepad (or other flat file editor), before you try with excel.

If this is indeed the issue, you can configure the excel connector to treat this field as string instead of number. other option would be to convert it to string and concat "" to the number. this way the spreadsheet will automatically treat it as string. afterwards you can convert it back to number in the spreadsheet. Thanks