Decimal places in SQL

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 开发者_如何学JAVAthe same work if i'd like 2 to be displayed as 2.000000?

Thanks!

---

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

---

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.

---

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

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

---

With regards to the reason why the result shows 100 instead of 100.4921875 is maybe related to the type of the corresponding column assuming that you store the result in a table column. Make sure that the type of that column is Double.

If you'd like 2 to be displayed as 2.000000, just multiply it by 1.0 as follows:

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

and the output will show: 100.49219