CODEWITHSUNDEEP
sql-serverarithmetic-expressions
Gloria Santin
Gloria Santin

Reputation: 2136

Arithmetic overflow error in SQL Server when the result is cast to a decimal

In the SELECT statement, I am trying to calculate a percentage with a precision of 1 decimal place.

This statement in the select statement returns an arithmetic overflow error:

CAST(((COUNT(TicketNbr)*100.00)/@TotalCount) AS Decimal (3,1)) END AS Percentage

The COUNT(TicketNbr) count value is 10.

The @TotalCount value is 10.

If I do not cast the result, the result is 1.0000. But when I use the CAST function, the query results an arithmetic overflow error.

Why does this error occur?

Upvotes: 0

Views: 625

Answers (1)

Squirrel
Squirrel

Reputation: 24763

the result for 10 * 100.0 / 10 is 100.0

it will required a minium of decimal(4,1) to hold the value.

You cast it as decimal(3,1) will result in the overflow error

Upvotes: 3

Related Questions