BigQuery - rounded outputs sometimes not really round

Question

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:

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

Answer 1

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