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

Question

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

wondering... what kind of problem are you working with that requires numbers above 5 quintillions? — Felipe Hoffa, Aug 15 '16 at 21:08
Hey I have migrated some data into BigQuery tables and am trying to validate that data. It has an integer type column which basically consists of IDs having 12 digits. So I am getting these error in calculating basic statistical metrics like mean, max, sum avg etc You have any other approach of data validation in mind? :) — abhishek jha, Aug 16 '16 at 03:32

score 3 · Answer 1 · answered Oct 11 '21 at 08:45

3

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

answered Oct 11 '21 at 08:45

ivospijker

702
1
7
22

This was really useful when crawling through Ethereum transactions with wei value precision! – Emerson Hsieh Feb 23 '22 at 14:19

score 2 · Accepted Answer · answered Aug 15 '16 at 06:29

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.

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

2 Answers2

Linked