How to calculate division with decimals in SQL

SQL division method to retain decimals: use CAST, ROUND or TRUNCATE function. The CAST function coerces a numeric type to preserve specific precision and scale. The ROUND function rounds floating point results. The TRUNCATE function truncates floating point results.

Calculation method of decimal-preserving division in SQL

In SQL, the division operator is a slash (/ ), which produces a floating point result. By default, floating point results are rounded to 15 digits after the decimal point.

Methods for retaining decimal places

In order to retain more or fewer decimal places, you can use the following methods:

• CAST function: Use this with the dividend or divisor to cast to a numeric type with specific precision and scale. For example:

SELECT CAST(numerator AS DECIMAL(10, 2)) / CAST(denominator AS DECIMAL(10, 2));

• ROUND Function: is used to round floating point results. For example:

SELECT ROUND(numerator / denominator, 2);

• TRUNCATE function: is used to truncate floating point results. For example:

SELECT TRUNCATE(numerator / denominator, 2);

Example

Consider the following query:

SELECT 10 / 3;

By default, this query returns a floating point result of 3.3333333333333335.

If you want to keep two decimal places, you can use the CAST function:

SELECT CAST(10 AS DECIMAL(10, 2)) / CAST(3 AS DECIMAL(10, 2));

This query will return 3.33.

The above is the detailed content of How to calculate division with decimals in SQL. For more information, please follow other related articles on the PHP Chinese website!