Error Message

Question

Post reply

Error Message

denimblue

Mr or Mrs. 500

Points: 594
More actions
October 6, 2004 at 11:05 am

#161643

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to dta type int.
The query I did to get the above
Select sum(filesize) as MB, sum(filesize)/ 1000 as GB
from o_file
where volid = 2 and groupid = 200

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Try CAST(SUM(FileSize)) AS BIGINT) MB, CAST(SUM(FileSize)/1000 AS BIGINT) GB

Good Hunting!

AJ Ahrens

webmaster@kritter.net

denimblue Mr or Mrs. 500 Points: 594 More actions · Answer 2

CAST(SUM(FileSize)) AS BIGINT) MB,

CAST(SUM(FileSize)/1000 AS BIGINT) GB

from o_file

where volid = 16 and groupid = 221

I get incorrect syntax near the keyword 'sum'

cslaughter@proficiotech.com Valued Member Points: 53 More actions · Answer 3

cslaughter@proficiotech.com

Valued Member

Points: 53

October 6, 2004 at 4:16 pm

#525684

You have an extra ) in your first line.

denimblue Mr or Mrs. 500 Points: 594 More actions · Answer 4

Are you saying

CAST(SUM(FileSize)AS BIGINT) MB, CAST(SUM(FileSize)/1000 AS BIGINT) GB from o_file where volid = 16 and groupid = 221

Because I still get, "incorrect syntax near the keyword 'sum'"

cslaughter@proficiotech.com Valued Member Points: 53 More actions · Answer 5

That was just what I noticed in the syntax.

But based on your original post, is it possible that not all of your data within column FileSize is numeric?

In which case you'd want to use the CAST function within the SUM:

SUM(CAST(FileSize AS BIGINT)) AS MB, SUM(CAST(FileSize AS BIGINT)/1000) AS GB

kit80 SSC Veteran Points: 225 More actions · Answer 6

I assume you have "SELECT" in front of the statement, i.e. :

SELECT CAST(SUM(FileSize)AS BIGINT) MB, CAST(SUM(FileSize)/1000 AS BIGINT) GB

from o_file

where volid = 16 and groupid = 221

and not:

CAST(SUM(FileSize)AS BIGINT) MB, CAST(SUM(FileSize)/1000 AS BIGINT) GB

from o_file

where volid = 16 and groupid = 221

denimblue Mr or Mrs. 500 Points: 594 More actions · Answer 7

SELECT CAST(SUM(FileSize)AS INT) MB, CAST(SUM(FileSize)/1000 AS INT) GB

from o_file

where volid = 16 and groupid = 221

Server: Msg 8115, Level 16, State 2, Line 1

Arithmetic overflow error converting expression to data type int.

It would not take bigint so I just did int, but still get error:

vadba SSChampion Points: 11133 More actions · Answer 8

You need to cast to bigint BEFORE summing:

SELECT SUM( Cast(FileSize AS bigint) ) AS MB,

SUM( Cast(FileSize AS bigint)/ Cast(1000 as bigint)) AS GB

FROM o_file

WHERE volid = 16 AND groupid = 221

denimblue Mr or Mrs. 500 Points: 594 More actions · Answer 9

SELECT SUM( Cast(FileSize AS bigint) ) AS MB,

SUM( Cast(FileSize AS bigint)/ Cast(1000 as bigint)) AS GB

FROM o_file

WHERE volid = 16 AND groupid = 221

Server: Msg 409, Level 16, State 2, Line 1

The sum or average aggregate operation cannot take a void type data type as an argument.

Server: Msg 409, Level 16, State 1, Line 1

The sum or average aggregate operation cannot take a void type data type as an argument.

Server: Msg 243, Level 16, State 1, Line 1

Type bigint is not a defined system type.

Server: Msg 243, Level 16, State 1, Line 1

Type bigint is not a defined system type.

Server: Msg 243, Level 16, State 1, Line 1

Type bigint is not a defined system type.

vadba SSChampion Points: 11133 More actions · Answer 10

Debbie, are you using SQL Server 2000? BigInt is new to SQL Server 2000 and is not available in earlier versions of SQL Server.

I tested the following without any problems:

drop table o_file

go

create table o_file (id int identity(1,1), filesize int, volid int, groupid int)

go

set nocount on

insert o_file (filesize, volid, groupid) values (12345, 1, 1)

insert o_file (filesize, volid, groupid) values (123456, 1, 2)

insert o_file (filesize, volid, groupid) values (123457, 1, 3)

insert o_file (filesize, volid, groupid) values (12345678, 1, 4)

insert o_file (filesize, volid, groupid) values (123456789, 16, 221)

insert o_file (filesize, volid, groupid) values (1234565432, 16, 221)

insert o_file (filesize, volid, groupid) values (null, 16, 221)

set nocount off

go

SELECT SUM( Cast(FileSize AS bigint) ) AS MB,

SUM( Cast(FileSize AS bigint)/ Cast(1000 as bigint) ) AS GB

FROM o_file

WHERE volid = 16 AND groupid = 221

Mike

denimblue Mr or Mrs. 500 Points: 594 More actions · Answer 11

denimblue

Mr or Mrs. 500

Points: 594

October 7, 2004 at 12:58 pm

#525837

I'm running MSSQL 7. Anyway I can go around this?

vadba SSChampion Points: 11133 More actions · Answer 12

About your only option left is the DECIMAL data type:

SELECT SUM(Cast(FileSize AS Decimal(38))) AS MB,

Cast(SUM(Cast(FileSize AS Decimal(38))/ 1000) as Decimal(38)) AS GB

FROM o_file

WHERE volid = 16 AND groupid = 221

denimblue Mr or Mrs. 500 Points: 594 More actions · Answer 13

Enthusiast

Thank you. That does work. my results were has follows

MD 3205362126 GB 3205362

Would you say that's 32GB?

vadba SSChampion Points: 11133 More actions · Answer 14

>>MD 3205362126 GB 3205362

>>Would you say that's 32GB?

Close enough. Technically speaking, 1KB=1024 bytes, 1MB=1024KB=1048576 bytes, 1GB=1024MB=1073741824 bytes. So 3205362126 MB actually equals 3130236 (rounded). You would divide by 1024 instead of 1000.