CODEWITHSUNDEEP
sql-serveraggregate-functionsouter-join
Mahmood Dehghan
Mahmood Dehghan

Reputation: 8265

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

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?

Upvotes: 2

Views: 1829

Answers (2)

BICube
BICube

Reputation: 4681

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.

Upvotes: 3

Andomar
Andomar

Reputation: 238106

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

Upvotes: 2

Related Questions