Decimal places in SQL

Question

I am calculating percentages. One example is coming down to 38589/38400

So the percentage is 100*(38589/38400) which equals something like 100.4921875, but the result shows up as 100.

How can I get it to be displayed with x number of decimals?

Similarly, will the same work if i'd like 2 to be displayed as 2.000000?

Thanks!

Answer 1

You can cast it to a specific data type, which 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

With regards to your number formatting have 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'

so to get 2.000000 from 2 do:

SELECT FORMAT(2,6);

Also, according to mySQL's documentation regarding division:

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

These rules are applied for each operation, such that nested calculations imply the precision of each component. Hence, (14620 / 9432456) / (24250 / 9432456), resolves first to (0.0014) / (0.0026), with the final result having 8 decimal places (0.60288653).

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