Arithmetic calculation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Arithmetic calculation

Author

Topic

schinni
Yak Posting Veteran

66 Posts

Posted - 2003-10-17 : 17:42:52

Hello,

I have a column with data type int and has about 100,000 rows
i am trying to sum up the column and i am getting arthmetic
overflow error ....

I am using bigint is there any datatype I should use for this
to accomodate this big sum.

Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-17 : 17:52:19

Even if all of your rows had the maximum value of an int, which is 2147483647, you still would not reach the BIGINT limit for 100,000 rows.



SET NOCOUNT ON

CREATE TABLE Table1
(
Col1 INT NOT NULL
)

DECLARE @num INT

SET @num = 1

WHILE @num <= 100000
BEGIN
	INSERT INTO Table1 VALUES(2147483647)

	SET @num = @num + 1
END

SELECT SUM(CONVERT(BIGINT, Col1))
FROM Table1

DROP TABLE Table1

You just need to convert the column to a BIGINT before you SUM it up, like in my example.

BTW, the code took about 25 seconds to run on my laptop.

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-17 : 18:23:56

If you would post your query that generated the overflow, there may be some obvious error. The DDL for the Table could be helpful.

Here's what I'd try

SELECT SUM(MyColumn) FROM MyTable

Maybe you're not casting your column to BIGINT before performing the sum? Can't tell without looking at the code.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-17 : 18:26:35

quote:
Originally posted by SamC

Maybe you're not casting your column to BIGINT before performing the sum? Can't tell without looking at the code.

Sam, isn't that what I said?

Only difference is that I said CONVERT instead of CAST.

From my example:

SELECT SUM(CONVERT(BIGINT, Col1))
FROM Table1

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-17 : 18:44:13

Hi Tara. I didn't read the end of your post. Sorry.

I wonder if we'll ever find out how the arithmetic overflow was generated?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-17 : 18:47:30

It was probably generated like this:

DECLARE @Var1 BIGINT

SELECT @Var1 = SUM(Col1)
FROM Table1

PRINT @Var1

Tara

schinni
Yak Posting Veteran

66 Posts

Posted - 2003-10-17 : 22:01:51

Tara,

That worked ,i was not doing the convert or cast correctly

Thanks ...