Arithmetic overflow error converting expression to data type int

Under certain circumstances I am getting the following error

"Arithmetic overflow error converting expression to data type int"

when running the following code:

SELECT Count(*), Sum(GrossWinAmount)
FROM LGSLog
WHERE
(CurrentDate >= '9/1/2004 8:00:00 AM') And (CurrentDate <= '9/27/2004 7:59:59 AM')

If I remove the "Sum(GrossWinAmount)" from the select, it works fine. I therefore believe that Sum is causing the error. Is there a version of Sum that works with larger variables, such as a BigInt? If not, is there some way to do the equivalent using larger numbers? I need to allow for the possibility of obtaining one month's summary, and sometimes the summary value is apparently too large for Sum to handle.My first suggestion would be:SELECT Count(*), Sum(Cast(GrossWinAmount AS MONEY))
FROM LGSLog
WHERE CurrentDate >= '9/1/2004 8:00:00 AM'
And CurrentDate <= '9/27/2004 7:59:59 AM'-PatP|||I don't think that would accomplish anything in this case since floating point and monitary values such as this are stored in the database, for the most part, as an SQL int type (multiplied by 100 and rounded in the program(s) before being written... don't ask why... it was being done this way before I started working on the system).|||Did you try it, or are you just guessing that it won't work?

-PatP|||Yep. I just tried changing the query as follows (I changed the date range to only include a week's worth of data... in the program, these are actually datetime variables)...
:
declare @.Totals TABLE
(
GameCount int default 0,
Win int default 0,
Adj int default 0,
Bet int default 0
)
declare @.Count int
INSERT INTO @.Totals
SELECT Count(*) As GameCount, Sum(CAST(l.GrossWinAmount AS MONEY)) As Win, Sum(CAST(l.AdjustedWinAmount AS MONEY)) As Adj, Sum(CAST(l.TotalBetAmount AS MONEY)) As Bet
FROM LGSLog l
WHERE (CurrentDate >= '9/20/2004 8:00:00 AM') And (CurrentDate <= '9/28/2004 7:59:59 AM')
GROUP BY l.MasterID
ORDER BY l.MasterID
SELECT @.Count=COUNT(*) FROM @.Totals
if (@.Count = 0)
Begin /* make sure something valid is returned if nothing found */
INSERT INTO @.Totals VALUES (0, 0, 0, 0)
End
SELECT * FROM @.Totals
:
and got the following error...
:
There is insufficient result space to convert a money value to int.
The statement has been terminated.
:
Which suggests to me that Sum is still wanting to RETURN an int size value, which is what I thought. The theory is that it is not the size of what is being passed into Sum(), but the fact that Sum() is trying to return a value that is too big because it wants to return a value the size of an int!|||How about the DDL for LGSLog

And how many rows are in the result set?

What are the MIN and MAX Values for those columns?

What is the average wind-speed velocity of a sparrow?|||Could it be that the result went outsideof the alloweable boundaries (922337203685477.5807 or -922337203685477.5808)?|||I think I finally got it to work. It took a hybrid of your original suggestion. I cast the sum results to money values / 100 (floats gave me too imprecise a number). I also changed the result set data fields to money values. Then, in the program, I multiplied by 100 and rounded and then converted to long integer (the program already expcected the values as a fixed point type stored in a long... fixed 2 places left of the right most digit... it was easier this way than to make major mods throughout program). I just hope this does not introduce rounding errors, which was the main reason for storing as fixed point values converted to integers in the first place.

This technique seemed to work getting data as far back as one month (no overflow/conversion errors).

declare @.Totals TABLE
(
GameCount int default 0,
Win money default 0,
Adj money default 0,
Bet money default 0
)
declare @.Count int
INSERT INTO @.Totals
SELECT Count(*) As GameCount, Sum(CAST(l.GrossWinAmount AS money) / 100) As Win,
Sum(CAST(l.AdjustedWinAmount AS money) / 100) As Adj, Sum(CAST(l.TotalBetAmount AS money) / 100) As Bet
FROM LGSLog l
WHERE (CurrentDate >= '9/01/2004 8:00:00 AM') And (CurrentDate <= '9/28/2004 7:59:59 AM')
GROUP BY l.MasterID
ORDER BY l.MasterID
SELECT @.Count=COUNT(*) FROM @.Totals
if (@.Count = 0)
Begin
INSERT INTO @.Totals VALUES (0, 0, 0, 0)
End
SELECT * FROM @.Totals

The grouping broke the results into anywhere from a couple of rows to over 25, depending on how far back I wanted to go. This also helped to reduce the size of the value being summed at the server. If the number got too big in the program, I could just use a larger integer size, such as an int32 or int64 in the program, which is not an option I had at the server.|||Instead of casting to MONEY and doing the divide by 100 then multiplying the sum by 100, just cast the column to BIGINT which is an int64 equivalent.

I'm still having trouble getting my head around the idea that you overflowed a MONEY... MONEY is big enough to express the US national debt in Argentine Pesos!

-PatP|||I don't think it actually "OVERFLOWED" the money type. I think it had no problem working with money. I think what was happening is that when "Sum" RETURNED the sum value, SQL tried to convert the "money" value to an "int" type, overflowing the "int" type during the conversion. This is why I think that the fields in the result set had to go from the integerial type to a money. I think I tried converting the value being passed into Sum, and also tried storing the result into a bigint, but never both at the same time. It always resulted in a conversion to an integer at some point... going in or coming out of Sum.

Apparently, if you pass an int type into Sum, it is going to return an int, which is what I was doing originally. If you pass in a money, it will sum internally as a money type and return a money type. But if the receiver variable is an int, it will downcast to an int, overflowing the resulting value. If you store the result into a money type, then it is going straight from a money to a money... no downcast necessary.|||For what it is worth, MONEY actually stores as an 8 byte signed integer, with implicit scaling by 1e4 (basically four digits after the decimal place). If your application is dealing with ODBC data directly like a C++ program would, you'd see the result come back as a long int that needed to be divided by 10000 (or in your case only 100).

-PatP