sql - Rounding of the numeric values -

i want round of values of 2 columns:

select a.region "regions",        a.suminsured,2 "suminsured" ,        a.suminsured/b.sum*100 pct  (     select  region, sum(suminsured) suminsured      "exposure_commune" group region ) a, (select sum(suminsured) "exposure_commune") b 

i want suminsured , pct columns come 2 decimal places. can tell me should do?

use round() 2 parameters, works data type numeric.

while being @ it, query can simpler , faster:

select region      , round(sum(suminsured), 2) suminsured      , round((sum(suminsured) * 100) / sum(sum(suminsured)) on (), 2) pct   "exposure_commune" group  1; 

you can use sum() window function total without additional subquery, cheaper. related:

• postgres window function , group exception

multiplying first typically cheaper , more exact (although barely matters numeric).

data type not numeric

for data types double precision of real can ...

• just cast numeric use same function.
• multiply 100, cast integer , divide 100.0.
• multiply 100 , use simple round() , devide 100.

the simple round() 1 parameter works floating point types well.

demonstrating 3 variants:

select region      , round(sum(suminsured), 2) suminsured      , (sum(suminsured) * 100)::int / 100.0 suminsured2      , round(sum(suminsured) * 100) / 100 suminsured3      , round((sum(suminsured) * 100) / sum(sum(suminsured)) on (), 2) pct       , ((sum(suminsured) * 10000) / sum(sum(suminsured)) on ())::int / 100.0 pct2      , round((sum(suminsured) * 10000) / sum(sum(suminsured)) on ()) / 100 pct3  "exposure_commune" group  1; 

sql fiddle.