Why is SUM(???) responding with Arithmetic overflow 1

[uniq1]

Running the following code on our test DB works fine, but on our live system errors with :

'Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.'

The code is :
Select
sum(ads_doc_pages.page_size) as 'Space Used',
count(ads_doc_pages.doc_no)as 'Total Pages',
tw_ctgry.ctgryname as 'Category'
From tw_docu0,tw_ctgry,ads_doc_pages
Where tw_docu0.ctgryno = tw_ctgry.ctgryno
and tw_docu0.location = 1088
and tw_docu0.docno = ads_doc_pages.doc_no
Group by tw_ctgry.ctgryname

It does not like the sum function but will only return an 'int'.
How can I 'get around' this ...

Any help most grateful

 

[Cagliostro]

does your query work if you remove sum? John Fill

ivfmd@mail.md

 

[uniq1]

If I change sum for count it works fine and gives :

Space USed Total Pages Category
----------- ----------- --------
850255 850255 ABC
225365 225365 DEF
379751 379751 HIJ
38527 38527 KLM
19946 19946 NOP
42467 42467 QRS
7418 7418 TUV

 

[Cagliostro]

In this case, if you have big numbers in the table, for example twice a 0xFFFFFFFF, the sum will be overflow. John Fill

ivfmd@mail.md

 

[uniq1]

No magic SQL get around then, that you know of

Thanks ... Nice to be reassured I havent't missed anything

 

[tlbroadbent]

Try converting the integer value to decimal before summing.

Example:

sum(cast(page_size As Decimal(20,0))) as 'Space Used' Terry
------------------------------------
People who don't take risks generally make about two big mistakes a year. People who do take risks generally make about two big mistakes a year. -Peter Drucker

 

[uniq1]

This worked fine

Thanks very much Terry