Sum (INT) error: Arithmetic overflow error when converting expression to data type int

In SQL Server, the data of a column is within the int range, but when sum is used to calculate the sum of the column, an Arithmetic overflow error occurs when expression is converted to an int type ".

First, let's take a look at the length of the data type defined by SQL Server:

• Bigint data type Stores numbers in the range of-2 ^ 63 (-9223372036854775808) to 2 ^ 63-1 (9223372036854775807. The storage size is 8 bytes.
• The storage range of the int data type is-2,147,483,648 to 2,147,483,647 (each value requires 4 bytes of storage space ).
• The storage range of smallint data types is only-32,768 to 32,767 (each value requires 2 bytes of storage space ), the tinyint data type can only store numbers ranging from 0 to 255 (each value requires a storage space of 1 byte ).

 

When we perform the sum operation on Int type data, Arithmetic overflow may occur. In this case, we can change the data type of the original column to bigint before performing the sum operation.

 

SQL statement:

1. Select sum (cast (colname as bigint) from tablename

2. Select sum (convert (Numeric (20, 0), colname) from tablename