Arithmetic overflow error converting int to data type numeric.

Question

Arithmetic overflow error converting int to data type numeric.

Christopher Jack 1,616

Hi,

I am trying to return a percentage number - the max values I am working with is 10,000

My code is

case when tt.[Under £20] <> 0 then
     ( CAST(tt.[Under £20] as numeric(5,5)) /CAST(tt.Total as numeric(5,5))*100) else 0
     end as '%',

Can anyone help?

Thanks

Accepted answer

1 additional answer

Your answer

Answer 1

Olaf Helper 47,436

converting int to data type numeric.

Your SQL code is more then a bit wrong.
numeric(5, 5) means a total size of 5 digits and 5 decimal place = not leading digit before decimal; that can't work.
Use numeric(5,0) or better numeric(10, 5).

See https://learn.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql?view=sql-server-ver15

Christopher Jack 1,616 Reputation points

2022-04-12T14:50:26.13+00:00
How would I round the number as it is returning

16.70000000000

I have tried

round(CAST(tt.[Under £20] as numeric(10,0)) /CAST(tt.Total as numeric(10,0))*100,1)

But that doesnt seem to work.

Solved it with

cast(CAST(tt.[Under £20] as numeric(10,0)) /CAST(tt.Total as numeric(10,0))*100 as numeric (10,0))

Thanks Olaf
Olaf Helper 47,436 Reputation points

2022-04-13T05:42:51.223+00:00

But that doesnt seem to work.

And what does this mean in detail?
Keep in mind, I don't have your database and I don't know your expected result.

Please post table design as DDL, some sample data as DML statement and the expected result.

Answer 2

Naomi Nosonovsky 8,431

Don't use numeric 5,5, use something like numeric(12,2) Or alternatively let SQL Server do conversion for you and just round the result, e.g.

cast(case when tt.[Under £20] <> 0 then
(tt.[Under £20]*1.0 /tt.Total)*100) else 0
end) as numeric(10,2)) as '%',

Share via

Arithmetic overflow error converting int to data type numeric.

1 additional answer

Your answer