2

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?

Mahmood Dehghan
  • 7,761
  • 5
  • 54
  • 71

2 Answers2

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.

BICube
  • 4,451
  • 1
  • 23
  • 44
  • The warning mislead me. I thought the outer join is the cause and I expected the `pays` column values to be much smaller. thanks. – Mahmood Dehghan Jun 08 '15 at 08:00
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
Andomar
  • 232,371
  • 49
  • 380
  • 404