How to calculate division with decimals in SQL

The division operator in SQL automatically rounds. When decimals need to be retained, you can use the ROUND() function or explicitly convert the data type to floating point.

SQL division preserving decimal method

In SQL, the division operator (/) will automatically operate on the result rounding. If you need to preserve decimals, you need to use the ROUND() function or explicitly convert the data type.

Use ROUND() function

SELECT ROUND(x / y, 2) FROM table_name;

ROUND() The first parameter of the function is the value to be rounded, and the second parameter is the number of decimal places to round to. For example, the above query will retain two decimal places.

Explicit conversion of data types

Another way to preserve decimals is to explicitly convert the result to a floating-point data type, such as FLOAT or DOUBLE. Floating-point data types can store decimals.

SELECT CAST(x / y AS FLOAT) FROM table_name;

Example

Consider the following table:

x	y
10	3

##Execute the following query:

SELECT ROUND(x / y, 2) FROM table_name;

The result is ：

x / y##3.33The query rounds the division result to two decimal places, and the result is 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!