Why can SUM over an INTEGER column overflow, and how do you prevent it portably?

## The failure A reporting query runs for two years and then, one Monday, starts failing: ```sql SELECT SUM(quantity) FROM order_lines; -- Arithmetic overflow error converting expression to data type int. ``` No code changed and no data is corrupt. The table simply grew until the total no longer fits the result type the engine chose for the sum. ## Where the result type comes from Standard SQL says `SUM` over an exact numeric argument produces an exact numeric result, but leaves the precision to the implementation. Engines resolve that differently, and the difference is the whole story: - **PostgreSQL widens.** `sum(smallint)` and `sum(integer)` return `bigint`; `sum(bigint)` returns `numeric`. Overflow on integer input is therefore not a practical concern. - **MySQL** returns a `DECIMAL` for exact-valued arguments and a `DOUBLE` for approximate ones, so integer sums also get room. - **SQL Server keeps the argument type.** `SUM` over an `int` column is typed `int`, and exceeding 2,147,483,647 raises the arithmetic overflow above. That is the same rule that makes an integer average truncate on some engines: one implementation-defined result type, two very different production bugs. ## The fix goes on the argument Widen the input so the accumulator itself is wide: ```sql SELECT SUM(CAST(quantity AS BIGINT)) FROM order_lines; ``` The common wrong fix casts the result: ```sql SELECT CAST(SUM(quantity) AS BIGINT) FROM order_lines; -- still overflows ``` The aggregate is evaluated first; if it overflows, the statement aborts before the outer conversion is ever reached. This mirrors the integer-average trap exactly — the operation that loses or breaks the value happens *inside* the function, so the repair has to happen before it. For values whose magnitude is genuinely unbounded, `CAST(x AS DECIMAL(38, 0))` gives far more headroom than a 64-bit integer at the cost of slower arithmetic. Choose deliberately: `BIGINT` covers essentially any counting measure; `DECIMAL` is for monetary totals where you also want exact base-10 scale. ## AVG is not a safe harbour It is tempting to think averaging dodges the problem because the answer is small. It does not: an average is a sum divided by a count, and on an engine that accumulates in the argument type, the intermediate sum can overflow before the division ever happens. The same argument-side cast fixes it: ```sql SELECT AVG(CAST(quantity AS DECIMAL(18,4))) FROM order_lines; ``` This version conveniently fixes both bugs at once — no overflow, and no truncated integer average. ## The floating-point variant Summing a `REAL` or `DOUBLE PRECISION` column has the opposite pathology. It will not raise an overflow at ordinary magnitudes, but binary floating-point addition is not associative: adding the same values in a different order can give a slightly different total, and adding many small values to a large running total loses low-order bits entirely. Nothing errors; the number is just a little wrong, and it may be a *different* little wrong on the next run. For any measure that a human reconciles — money above all — store and sum `DECIMAL`/`NUMERIC` rather than a binary float. Reserve floating point for measurements where relative precision is what matters. ## Making it a habit rather than a fix Because the failure is time-delayed, catching it in review beats catching it in production. Practical rules that scale: 1. **Type the column for the total, not the row.** If a per-row `quantity` never exceeds a few thousand but the lifetime total will reach billions, the interesting range is the total's. 2. **Cast at the aggregate in any query that sums a counting column over an unbounded history**, on engines that do not widen. It costs nothing and documents the intent. 3. **Treat "it worked yesterday" as evidence, not reassurance.** Overflow in an aggregate is a function of accumulated row count, so the query that has run nightly for two years is exactly the one at risk. 4. **Be explicit at the boundary too.** A 64-bit total handed to a client typed for 32-bit integers overflows outside the database instead, with even less diagnostic value. ## Answering it well Name the mechanism first — the result type is derived from the argument type and its width is implementation-defined — then say which engines widen and which do not, then put the cast on the argument and explain why the result cast fails. Adding that `AVG` shares the hazard, and that floating-point sums fail silently rather than loudly, is what separates a senior answer from a correct one.