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?

BICube · Accepted Answer · 2015-06-07 20:45:07Z

3

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.

answered Jun 7, 2015 at 20:45

BICube

4,7211 gold badge26 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mahmood Dehghan Tazereh Over a year ago

The warning mislead me. I thought the outer join is the cause and I expected the pays column values to be much smaller. thanks.

Andomar · Accepted Answer · 2015-06-07 20:44:59Z

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

answered Jun 7, 2015 at 20:44

Andomar

239k55 gold badges387 silver badges412 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related