Arithmetic overflow problem

Question

i have a table in my database as freespace... it has a column called freesize with its datatype as int.

i have three valuse in it :

1065189988

1073741818

1073741819

now i try to get the total free space but i get an error.

private void GetFreeSpace()
    {
        DataTable dt = new DataTable();
        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = ConfigurationManager.ConnectionStrings["SumooHAgentDBConnectionString"].ConnectionString;
        connection.Open();
        SqlCommand sqlCmd = new SqlCommand("select sum(freesize)* 0.000000000931322575 as freespace from freespace", connection);
        SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
        sqlDa.Fill(dt);
        connection.Close();
        if (dt.Rows.Count > 0)
        {
          double  freeSpace = Convert.ToDouble(dt.Rows[0]["freespace"].ToString());
        }
    }

Arithmetic overflow error converting expression to data type int.

Line 123:            SqlCommand sqlCmd = new SqlCommand("select sum(freesize)* 0.000000000931322575 as freespace from freespace", connection);
Line 124:            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
Line 125:            **sqlDa.Fill(dt);** here i get error
Line 126:            connection.Close();
Line 127:            if (dt.Rows.Count > 0)

Any suggestions of how to get the value in freespace without changing the datatype?

Thanks

I know it works good with bigint, but I want int.

Answer 1

Multiply by C < 1, and then sum.

Answer 2

So you're summing 3 large integers, then multiplying by a tiny double, but still want the output to be an int?

you could retrieve each row as an int, then do your math in c#

or you could cast the int to a bigint or double before the sum, so you don't overflow there.

Answer 3

You've got two choices as far as I can tell: use Li0liQ's approach, something on the order of

SELECT SUM(freesize * 0.000000931322575) ...

which may suck because of accuracy concerns; or, sucking it up and using a bigint. That raises the question: why are you so concerned with using an int rather than a bigint? If all you have is a hammer, then go nuts. But I'd at least consider using bigint if there isn't a compelling reason not to.

Answer 4

The sum of the 3 values alone are larger than the data type size for an Int in SQL which is defined as the range: -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)

As such you could do the following:

select cast(sum(cast(freesize as bigint))* 0.000000000931322575 as int) as freespace from freespace

Which will support the math and get you back the result as an INT but this cast will just truncate the value and return 2 while as a double it would be 2.99 so you would also want to have SQL round the number appropriately.

Answer 5

Multiply first, then SUM?

select cast(sum(freesize* 0.000000000931322575) as int)...

However, as Adam Gritt pointed out, do you want 2 or 3 as your answer: round or truncate? So, expanding on is answer... To round correctly as deal with truncation, add 0.5 before the cast back to int

select cast(sum(freesize* 0.000000000931322575) + 0.5 as int)...