Sum exceeds 32,767

Question

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?

paross1 · Accepted Answer

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

CF_Oracle · Answer

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.