Search code examples
sql-serverarithmetic-expressions

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?

Solution

  • 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