MSSQL sum()计算expression转化为数据类型int时发生算术溢出错误解决

SQL Server sum()函数计算某一列的值的合时

select sum(pdfsize)/1024/1024 as totalsize,count(*) as cnt,sum(pdfsize)/1024/1024/count(*) as avgsize

由于表行数过大（接近5千万行），查询一段时间后报出

expression转化为数据类型int时发生算术溢出错误

错误

应该是超过int数据不够存了

逐将数据类型转换为numeric类型

select  sum(convert(numeric(20,0),pdfsize)/1024/1024)   as totalsize ,count(*) as cnt  , sum(convert(numeric(20,0),pdfsize)/1024/1024) /count(*)  as avgsize   from main0  where (pdfsize is not NULL)

计算果然没出错

转载于:https://blog.51cto.com/skybug/1349580