# Decimal

The Decimal type is useful for applications that require exact decimal representations, such as financial calculations or scientific computations.

We can use `DECIMAL(P, S)`

to indicate decimal types.

`P`

is the precision, which is the total number of digits in the number, it's range is [1, 76].`S`

is the scale, which is the number of digits to the right of the decimal point, it's range is [0, P].

If `P`

is less than 38, the physical datatype of decimal is `Decimal128`

, otherwise it's `Decimal256`

.

For a DECIMAL(P, S) data type:

- The minimum value is
`-10^P + 1`

divided by`10^S`

. - The maximum value is
`10^P - 1`

divided by`10^S`

.

If you have a `DECIMAL(10, 2)`

, you can store values with up to `10 digits`

, with `2 digits`

to the right of the decimal point. The minimum value is `-9999999.99`

, and the maximum value is `9999999.99`

.

## Examples

`-- Create a table with decimal data type.`

CREATE TABLE decimal (

value DECIMAL(36, 18)

);

-- Insert two values.

INSERT INTO decimal

VALUES

(0.152587668674722117),

(0.017820781941443176);

-- Select all values from the table.

SELECT * FROM decimal;

Result:

`┌────────────────────────────┐`

│ value │

├────────────────────────────┤

│ 0.152587668674722117 │

│ 0.017820781941443176 │

└────────────────────────────┘

## Precision Inference

DECIMAL has a set of complex rules for precision inference. Different rules will be applied for different expressions to infer the precision.

### Arithmetic Operations

Addition/Subtraction:

`DECIMAL(a, b) + DECIMAL(x, y) -> DECIMAL(max(a - b, x - y) + max(b, y) + 1, max(b, y))`

, which means both integer and decimal parts use the larger value of the two operands.Multiplication:

`DECIMAL(a, b) * DECIMAL(x, y) -> DECIMAL(a + x, b + y)`

.Division:

`DECIMAL(a, b) / DECIMAL(x, y) -> DECIMAL(a + y, b)`

.

### Comparison Operations

- Decimal can be compared with other numeric types.
- Decimal can be compared with other decimal types.

### Aggregate Operations

- SUM:
`SUM(DECIMAL(a, b)) -> DECIMAL(MAX, b)`

- AVG:
`AVG(DECIMAL(a, b)) -> DECIMAL(MAX, max(b, 4))`

where `MAX`

is 38 for decimal128 and 76 for decimal256.

## Adjusting Result Precision

Different users have different precision requirements for DECIMAL. The above rules are the default behavior of databend. If users have different precision requirements, they can adjust the precision in the following ways:

If the expected result precision is higher than the default precision, adjust the input precision to adjust the result precision. For example, if the user expects to compute AVG(col) to get DECIMAL(x, y) as the result, where col is of type DECIMAL(a, b), the expression can be rewritten as `cast(AVG(col) as Decimal(x, y)`

or `AVG(col)::Decimal(x, y)`

.

Note that in the conversion or calculation of decimal types, if the integer part overflows, an error will be thrown, and if the precision of the decimal part overflows, it will be directly discarded instead of being rounded.