SQL: left join and sum: Arithmetic overflow error converting expression to data type int

Question

I have this query:

select p.UserName, sum(b.PercentRials) as amount, sum(r.Amount) as Pays
from bills b inner join UserProfiles p on b.PayerUserName=p.UserName
left outer join PayReceipts r on p.UserName=r.UserName
where p.[Percent]>0 and b.PayDate>'2014-11-20'
group by p.UserName

I get this error when running it:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation.

I can understand that the outer join causes this error because when I remove the last sum, it runs OK. but I remember doing such queries and getting NULL for summation on outer join table.

What can I do?

Answer 1

Try this

select p.UserName, sum(b.PercentRials) as amount, sum(CAST (r.Amount AS BIGINT)) as Pays
from bills b inner join UserProfiles p on b.PayerUserName=p.UserName
left outer join PayReceipts r on p.UserName=r.UserName
where p.[Percent]>0 and b.PayDate>'2014-11-20'
group by p.UserName

Type of expression in SUM determines the type of the output. So when your sum exceeds the integer limit, you will receive this error. To perceive your data as integer without getting the error, you need to cast the amount column to be BIGINT and therefore the sum will be of type BIGINT.

Answer 2

If you work at a financial firm,

sum(r.Amount) as Pays

might very well overflow the meagre integer limit of two billion. You could cast to a larger variable type, like a decimal:

sum(cast(r.Amount as decimal(38,2))) as Pays