Decimal places in SQL

Question

I calculate the percentages. One example comes down to 38589/38400

Thus, the percentage is 100 * (38589/38400), which is 100.4921875, but the result is displayed as 100.

How can I make it display with x number of decimal places?

In the same way, the same thing would work if I wanted 2 to display as 2.000000?

Thanks!

Answer 1

You can apply it to a specific data type that preserves the data type, as well as rounding to a certain precision.

select cast(100*(38589/38400) as decimal(10,4)) 

Fyi

 select 100*(38589/38400) # returns 100.4922, not 100 for me select cast(2 as decimal(20,6)) # output : 2.000000 

Answer 2

As for your number formatting, you looked at the format function:

 mysql> SELECT FORMAT(12332.123456, 4); -> '12,332.1235' mysql> SELECT FORMAT(12332.1,4); -> '12,332.1000' mysql> SELECT FORMAT(12332.2,0); -> '12,332' 

to get 2.000000 out of 2:

 SELECT FORMAT(2,6); 

Also, according to mySQL documentation regarding division:

In the division performed with /, the scale of the result when using two operands of the exact value is the scale of the first operand plus the value of the system div_precision_increment variable (by default it is 4). For example, the result of the expression 5.05 / 0.014 has a scale of six decimal places (360.714286).

These rules apply to each operation, such that nested calculations assume the accuracy of each component. Therefore, (14620/9432456) / (24250/9432456) resolves first to (0.0014) / (0.0026), with the final result with 8 decimal places (0.60288653).

This will lead me to agree with @Cyberwiki regarding the result you will see from your unit.

Answer 3

You need to convert one of the types to a floating point:

 SELECT 100.0 * ((338589 * 1.0)/38400) ... 

Answer 4

As for the reason why the result shows 100 instead of 100.4921875, it might be related to the type of the corresponding column, assuming you save the result in a table column. Make sure the type of this column is Double.

If you want 2 to display as 2.000000, just multiply it by 1.0 as follows:

 (select (100*(38589/38400*1.0)) 

and the output will show: 100.49219