Requirements: Divide two integers, keep two decimal places and round them, and turn them into percentage form, that is, 4/5=0.80=80%
1. Divide two integers:
idn_dw=> select 4/5; ?column? ---------- 0 (1 row)
In SQL operation, "/" means to divide and round, so that the decimal part will be discarded.
2. Use cast to convert the divisor into a decimal
idn_dw=> select cast(4 as numeric)/5; ?column? ------------------------ 0.80000000000000000000 (1 row)
It can also be simplified: "::" in pg means conversion
idn_dw=> select 4::numeric/5; ?column? ------------------------ 0.80000000000000000000 (1 row)
3. Round and keep two decimal places
idn_dw=> select round(cast(4 as numeric)/5,2); round ------- 0.80 (1 row)
4. Zoom in 100 and convert to percentage form
idn_dw=> select concat(round(4::numeric/5,2)*100,'%'); concat -------- 80.00% (1 row)
However, the decimal part is not needed, adjust the order
idn_dw=> select concat(round(4::numeric/5*100),'%'); concat -------- 80% (1 row)
It's done.
Supplement: Use postgresql's round() round() function to report an error
need:
Round() using postgresql to retain two decimal places
Report an error:
HINT: No function matches the given name and argument types. You might
Solution:
Use the cast function to convert the value that needs to be rounded to numeric. If you convert it to other types, you may report an error.
Example:
round(cast(Calculation results) as numeric), 2)
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.