How to solve Msg 8115, Level 16, State 2, Line 2 Arithmetic overflow error converting expression to data type int.?

Question

I have a formula like this. But the formula has an error. Please help me.

select 
    [DAY] as [DAY],
    [Name] as [Name],
    ((cast([columnA] + [columnB] + [columnC] as bigint) * 1000) / NULLIF(8 * 1024 * 1048576, 0)) as [TotalColumn]
from 
    [TableA]

Error message:

Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.

Answer 1

Since your dividend is a bigint I suspect you will need the divisor to also be a bigint. Since you have some integer literals the math will attempt to put that in an int and it is too large. You can however force the divisor to be a bigint.

convert(bigint, 8) * 1024 * 1048576