You can receive this error when you want to get a total result by using the SUM function. In order to avoid this error, you must convert the column in the SUM function to bigint type using CAST. Let’s clarify the subject by making an example.
Let’s create a table that has Name and Count columns and add a few records as follows. As you can see in the Script, the values we add to the Count column force the limits of the int data type.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE [dbo].[ConvertExample]( [Name] [varchar](250) NULL, [Count] [int] NULL ) ON [PRIMARY] INSERT INTO [dbo].[ConvertExample] ([Name] ,[Count]) VALUES ('Nurullah CAKIR',2050512989) INSERT INTO [dbo].[ConvertExample] ([Name] ,[Count]) VALUES ('Hakan GURBASLAR',2050512988) INSERT INTO [dbo].[ConvertExample] ([Name] ,[Count]) VALUES ('Faruk ERDEM',2050512987) INSERT INTO [dbo].[ConvertExample] ([Name] ,[Count]) VALUES ('Dilara AYDIN',2050512987) |
When we sum count column with a query such as the following by using the SUM function, we will receive the error that is subject to our article because the total value will exceed the limit of the int type.
|
1 |
SELECT SUM(Count) FROM [dbo].[ConvertExample] |
When we change the query as follows, you will see that we can get the sum we want without error.
|
1 |
SELECT SUM(CAST(Count AS BIGINT)) AS COUNT_Column_Bigint FROM [dbo].[ConvertExample] |
