CODEWITHSUNDEEP
google-bigqueryrounding
Ilja
Ilja

Reputation: 1053

BigQuery - rounded outputs sometimes not really round

I'm running round command on BigQuery but the output is not always as round as expected. In addition it's not always the same values that are 'unrounded'.

For Example, the Public dataset query:

SELECT year,month,day, sum(round(weight_pounds,2))as total_pounds, count(*) as cnt
FROM [bigquery-public-data:samples.natality] 
group by 1,2,3
order by 1,2,3

Returns this output:

enter image description here

Is there a reason why the yellow marked values are not "fully rounded"? (If I rerun the query, other values might appear with .XX000001 or .XX99999 but the marked ones will be as expected rounded to the second decimal.

Thanks

Upvotes: 3

Views: 2942

Answers (1)

Liana Yos
Liana Yos

Reputation: 21

I suppose you need to receive the total sum rounded to two digits after the dot. Mathematically it is more precise to sum all the numbers and only then round the result. Regarding the fact that it is not close to the unrounded number, what do yo compare to? It should be compared to sum (weigth_pounds).

I appreciate you need to run the following query:

SELECT 
  year,month,day, 
  round(sum(weight_pounds),2) as total_pounds, 
  sum (weight_pounds) as total_pounds1 ,
  count(*) as cnt
FROM [bigquery-public-data:samples.natality] 
group by 1,2,3
order by 1,2,3

Upvotes: 2

Related Questions