Scale for decimal arithmetic

SQL statements can involve arithmetic expressions that use decimal data types of different precisions (the total number of digits, both to the left and to the right of the decimal point) and scales (the number of digits of the fractional component). The precision and scale of the resulting decimal type depend on the precision and scale of the operands.

Given an arithmetic expression that involves two decimal operands:
  • lp stands for the precision of the left operand
  • rp stands for the precision of the right operand
  • ls stands for the scale of the left operand
  • rs stands for the scale of the right operand
Use the following formulas to determine the scale of the resulting data type for the following kinds of arithmetical expressions:
  • multiplication

    ls + rs

  • division

    31 - lp + ls - rs

  • AVG()

    max(max(ls, rs), 4)

  • all others

    max(ls, rs)

For example, the scale of the resulting data type of the following expression is 27:
11.0/1111.33
// 31 - 3 + 1 - 2 = 27 
Use the following formulas to determine the precision of the resulting data type for the following kinds of arithmetical expressions:
  • multiplication

    lp + rp

  • addition

    2 * (p - s) + s

  • division

    lp - ls + rp + max(ls + rp - rs + 1, 4)

  • all others

    max(lp - ls, rp - rs) + 1 + max(ls, rs)

Related concepts
Numeric type overview
Related reference
Numeric type promotion in expressions
Storing values of one numeric data type in columns of another numeric data type