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.
| Author |
Topic |
|
ddamico
Yak Posting Veteran
76 Posts |
 Posted - 2011-04-05 : 12:56:08
|
| Good day,I am facing an issue regarding arithmetic overflow when performing aggregation of a money datatype. I have several accounts with balance informaiton stored as money. A household is comprised of several accounts. While trying to sum the account balances at the household level I have had one or more that have exceeded the size of the money datatype.I am wondering if anyone has an idea how to isolate these outliers in a query without having to go RBAR. Any help would be greatly appreciatted. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-05 : 13:02:33
|
You could doselect top 10 * from YourTable order by TheMoneyColumn asc to see the top 10 values, and change asc to desc to see the largest negative values.Money can hold plus or minus 922,337,203,685,477.5807. If the aggregation is causing overflow, it is most likely bad data. (You don't work for the division of the US department of treasury that is responsible for keeping track of national debt, do you?)  Are you using smallmoney? If so, the largest amount is lower.214,748.3647 |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-05 : 13:02:56
|
Is it stored as money or smallmoney? You're aggregating some pretty rich households if you overflowed money.  You can CAST as decimal is you need to:SELECT SUM(CAST(balance as decimal(38,4)) FROM myTableIf you're using smallmoney you can probably CAST as money and be fine. |
|
|
|
|
|
|
Arithmetic Overflow During Aggregation