Error Message

  • 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

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

     



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • 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'

  • You have an extra ) in your first line.

  • 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'"

  • 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

  • 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

  • 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:

  • 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

     

     

  • 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.

  • 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 (1234565432, 16, 221)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    insert o_file (filesize, volid, groupid) values (1234565432, 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

     

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

  • 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

  • Enthusiast

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

    MD 3205362126  GB 3205362

    Would you say that's 32GB?

  • >>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.

     

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

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