-2

I want to round of the values of two columns:

select a.region as "Regions",
       a.suminsured,2 as "SumInsured" ,
       a.suminsured/b.sum*100 as pct 
from (
    SELECT  region, sum(suminsured) as suminsured 
    FROM "Exposure_commune" group by region
) a,
(select sum(suminsured) FROM "Exposure_commune") b

I want the suminsured and pct columns to come with 2 decimal places. Can someone tell me what I should do?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
preeti
  • 11
  • 1
  • 6

2 Answers2

2

You can use directly numeric with two parameters. Second parameter for round decimal.

select sum(column_name::numeric(10,2)) from tablename
Eric Aya
  • 69,473
  • 35
  • 181
  • 253
Bharat Bhamare
  • 66
  • 6
0

Use round() with two parameters, which only works for the data type numeric.

While being at it, your query can be simpler and faster:

SELECT region
     , round(sum(suminsured), 2) AS suminsured
     , round((sum(suminsured) * 100) / sum(sum(suminsured)) OVER (), 2) AS pct 
FROM  "Exposure_commune"
GROUP  BY 1;

You can use sum() as window function to get the total without additional subquery, which is cheaper. Related:

Multiplying first is typically cheaper and more exact (although that barely matters with numeric).

Data type is not numeric

For data types double precision of real You can ...

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

The simple round() with just one parameter works for floating point types as well.

Demonstrating all three variants:

SELECT region
     , round(sum(suminsured), 2) AS suminsured
     , (sum(suminsured) * 100)::int / 100.0 AS suminsured2
     , round(sum(suminsured) * 100) / 100 AS suminsured3
     , round((sum(suminsured) * 100) / sum(sum(suminsured)) OVER (), 2) AS pct 
     , ((sum(suminsured) * 10000) / sum(sum(suminsured)) OVER ())::int / 100.0 AS pct2
     , round((sum(suminsured) * 10000) / sum(sum(suminsured)) OVER ()) / 100 AS pct3
FROM  "Exposure_commune"
GROUP  BY 1;

SQL Fiddle.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • thanx for your help but it is not working for me ..i have tried this but it throwing error function round(double precision, integer) does not exist – preeti Apr 01 '15 at 08:58
  • 1
    @preeti: Well, start by providing your version of Postgres and the table definition *in the question*. It's certainly working in modern Postgres, I added a demo. Ah .. **double precision** ... Did you see the part where I mention **only works for the data type numeric**? And did you see my comment where I ask for the **data type**? – Erwin Brandstetter Apr 01 '15 at 09:07