-1

I'm new in bigquery. I have tried to look for how can i resolve this problem but i didn't find the topic. Sorry if there is already one created.

I create a table with a Google Drive Sheets, i selected automatic format when I uploaded the info. So, I have this table:

  1. Camp name / Type / Mode
  2. Cost__EUR_ / INTEGER / NULLABLE

I just see my "cost" camp name doesn't have comma but in my sheets there is the comma. I need it because I use all the data in Euros.

The result is "65000" when the correct answer would be 650,00. I see NUMBERFORMAT function but I don't know how to use it.

How can I add my comma again?

I have this code:

SELECT 
  producto,
  SUM(Cost__EUR_) as Cost
FROM `test-261316.reach.cost_2019` 
WHERE
  producto = 'One'
GROUP BY
  producto

thanks so much!

nshabi
  • 63
  • 9
  • So essentially, you want 65000 to be presented as 650? Right? like if 65000 is not in euro but rather in cents? so why not to divide by 100 in this case? – Mikhail Berlyant Dec 08 '19 at 17:57
  • In your post, you say you "see" the cost column as having data which does not have a comma. Just to be clear, because number formatting varies across the world ... are you saying that you have a value of 65000 showing up when you expect to see a value of 650.00 (US decimal format) (i.e 65000 / 100)? – Kolban Dec 08 '19 at 17:57
  • In this case is 650,00€, but in other cases could be 652,12€. – nshabi Dec 08 '19 at 18:13
  • I just see if i change the region from Spain to US in my sheets and I create a new table in BigQuery I can see numbers with decimals with dot. – nshabi Dec 08 '19 at 18:24
  • First thing I would suggest is to ignore the SUM() in the select ... let us see what is in the original table. Describe exactly what you see in your spreadsheet, describe how you are creating a table from the sheet and described exactly what you see as a resulting BQ table. I'm going to guess (could be very wrong) that we will have a cell in your spreadsheet that reads 650,00 and the column in your table row will be 65000 (as opposed to 650.00 (US decimal)). – Kolban Dec 08 '19 at 18:34
  • This is one part of my two tables (both join in this sheets as example) https://docs.google.com/spreadsheets/d/1TSNLbUHvtuwxMW40vB30QRd8prEJYf78lMfX3uPqVV0/edit?usp=sharing I upload each Sheets to BG as new table, a normal process, automatic detection. BigQuery only work with dots in decimal numbers? – nshabi Dec 08 '19 at 22:20

3 Answers3

1

SELECT producto, FORMAT("%'d",SUM(Cost__EUR_)) as Cost FROM test-261316.reach.cost_2019 WHERE producto = 'One' GROUP BY producto

Sahil Sahay
  • 81
  • 1
  • 9
0

As stated in this public issue, BigQuery always treats commas as a thousand separator instead of a decimal one. Also, among BigQuery data types there are no mentions to a currency type. Instead, you could use ‘Integer’, ‘Numeric’ or ‘Float’ using a dot separator instead of a comma.

When ingesting financial data to BigQuery using schema auto-detection it would be advisable to modify the data type to ‘Numeric’ as it’s more suitable than the floating point types. (You will see further explanation in this StackOverflow thread).

Community
  • 1
  • 1
Joaquim
  • 406
  • 2
  • 10
0

This will give commas and decimals

,FORMAT("%'.2F",SUM(Cost__EUR_))

*Adjust the "2" for the number of decimals you want shown.

Reference: https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#width

enter image description here enter image description here

Logi
  • 1