MiniMe
Posts: 210 Joined: Thu Mar 15, 2001 4:30 pm
Post
by MiniMe » Fri Sep 13, 2002 1:26 pm
I have a table that has a column that is smallint. When I do a sum on that column it comes out to 25,770 when it should be closer to 7,361,830. Whats the deal?
John
Site Admin
Posts: 2622 Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:
Post
by John » Fri Sep 13, 2002 2:12 pm
It's summing in the type of the column, which will overflow. Try
sum(convert(col, 'long'));
John Turnbull
Thunderstone Software
mark
Site Admin
Posts: 5519 Joined: Tue Apr 25, 2000 6:56 pm
Post
by mark » Fri Sep 13, 2002 2:12 pm
smallint will only hold 16 bit values so it rolls over when summing. convert to a data type large enough for the sum.
sum(convert(Field,'int'))