Sum exceeds 32,767

[Ellen K]

I am trying to find the average call length in our database of support calls by
using "SUM(calltime) AS time_total" and then dividing that by the number of
incidents. The field I am searching on, calltime, is short integer.

If time_total is more than 32,767, it returns either an error or a negative
value depending upon the version of the database I'm using. I understand why
this is happening, but can't figure out how to fix it. I can't change the
database; it's enormous. Is there some way to specify that time_total needs to
be a long integer?

[CF_Oracle]

Database size is not the obstacle in this case (hopefully you have Oracle or SQL Server). You (or your DBA)could increase the column size to long integer without loosing data.

[paross1]

If you can't alter the datatype in the database, you might try casting calltime
as an int or bigint, something like

SUM(CAST(calltime AS int)) AS time_total
or perhaps
CAST(SUM(calltime) AS int) AS time_total

Phil

[Ellen K]

Not only is the database enormous but we have several other very important
office applications running off it, and any change could impact those other
applications. It's something that I can assure you the DBA and president will
NOT want to do.

My thought was that since SUM creates a generated column, is there some way to
specify what kind of column that is? If not, it's no big deal.

Thanks for your input.

[paross1]

Didn't you actually read my previous post? That is what I was recommending that you do. CAST the selected column to an INT or BIGINT within the query, NOT in the database.

Phil

[Ellen K]

Thanks, you were writing that as I was typing my other reply. Yes, that's the direction I was trying to go in. I am unfamiliar with CAST but will play around with that.

Thanks again!

[paross1]

Got it. Of course it depends on the database, and I was assuming SQL Server, so you may mave problems if you were using Access 97, or an old version of Oracle (pre-9i), etc.

Phil