CODEWITHSUNDEEP
google-bigquery
abhishek jha
abhishek jha

Reputation: 1095

How to avoid integer overflow error when applying sum() over large integer values in BigQuery

I am applying sum over an integer column which has some really large values.

I am constantly getting int64 overflow . Is there any way to avoid this overflow error

Upvotes: 5

Views: 6241

Answers (2)

ivospijker
ivospijker

Reputation: 732

Not sure if it was possible at the time that this question was asked, but now there is another option:

Casting the Int64 to a Numeric type will do the trick:

// Will overflow
sum(largeInteger) as sumLargeInteger

// Will work
sum(cast(largeInteger as numeric)) as sumLargeInteger

Upvotes: 5

Jeremy Condit
Jeremy Condit

Reputation: 7046

It depends on how you want to handle the error, but either way it seems like you'll need some form of approximation.

One approximation is to cast to a FLOAT64 before summing. Another is to divide by some suitable amount before summing. Which one you choose depends on what sort of input you have and what sort of precision you need from the output.

Upvotes: 2

Related Questions