How to Round an Average to 2 Decimal Places in Postgresql

How to round an average to 2 decimal places in PostgreSQL?

PostgreSQL does not define round(double precision, integer). For reasons @Mike Sherrill 'Cat Recall' explains in the comments, the version of round that takes a precision is only available for numeric.

regress=> SELECT round( float8 '3.1415927', 2 );
ERROR:  function round(double precision, integer) does not exist

regress=> \df *round*
                           List of functions
   Schema   |  Name  | Result data type | Argument data types |  Type  
------------+--------+------------------+---------------------+--------
 pg_catalog | dround | double precision | double precision    | normal
 pg_catalog | round  | double precision | double precision    | normal
 pg_catalog | round  | numeric          | numeric             | normal
 pg_catalog | round  | numeric          | numeric, integer    | normal
(4 rows)

regress=> SELECT round( CAST(float8 '3.1415927' as numeric), 2);
 round 
-------
  3.14
(1 row)

(In the above, note that float8 is just a shorthand alias for double precision. You can see that PostgreSQL is expanding it in the output).

You must cast the value to be rounded to numeric to use the two-argument form of round. Just append ::numeric for the shorthand cast, like round(val::numeric,2).

---

If you're formatting for display to the user, don't use round. Use to_char (see: data type formatting functions in the manual), which lets you specify a format and gives you a text result that isn't affected by whatever weirdness your client language might do with numeric values. For example:

regress=> SELECT to_char(float8 '3.1415927', 'FM999999999.00');
    to_char    
---------------
 3.14
(1 row)

to_char will round numbers for you as part of formatting. The FM prefix tells to_char that you don't want any padding with leading spaces.

Changing data type to float and rounding to 2 decimal digits

The answer depends on the actual datatype of column salary. The key point is that round() in Postgres does not allows floats (only numeric types are supported).

If you are dealing with a numeric datatype, then you can first round(), then cast to float:

round(avg(salary), 2)::float

If you are dealing with a float column, then you would need to cast the result of the aggregate function before using round() on it:

round(avg(salary)::numeric, 2)::float

PostgreSQL - rounding floating point numbers

You can accomplish this by doing something along the lines of

select round( (21.04 /0.05 ),0)*0.05

where 21.04 is the number to round and 0.05 is the accuracy.

Psql format number so that it always has two decimal places

Try this :

SELECT round( CAST(float8 '1200.3363636' as numeric), 2);
SELECT round( CAST(float8 '1200' as numeric), 2);

Select query custom calculated column to round to 2 decimal places

The division operation in postgresql truncates to integer value just found that

select round((4000/576::float),3) as result;

adding meta ::float to the division operation gives the desired result, it does not truncates the output to integer value.

Thanks

Is this the correct way to cast, round, and avg an object in SQL?

Formatted string

Use to_char() to get a formatted string without insignificant zeroes (or padding blanks) - "rounded to the tenth decimal place" as you commented:

SELECT to_char(round(avg(ratings.rating), 10), 'FM999999999990.9999999999')

Note the one 0. Typically you want that position in any case. Like for 0.3. Add as many 9 as you want to allow digits. The manual:

0 specifies a digit position that will always be printed, even if it
contains a leading/trailing zero. 9 also specifies a digit position,
but if it is a leading zero then it will be replaced by a space, while
if it is a trailing zero and fill mode is specified then it will be deleted.

And about the FM prefix:

fill mode (suppress leading zeroes and padding blanks)

Numeric value without insignificant trailing zeroes

Cast to double precision (float8) to get a numeric value without insignificant trailing zeroes. The cast trims insignificant zeroes. Generally, casting to a floating point number can introduce corner case rounding errors.

I had suggested more sophisticated solutions first, but since you are only interested in precision up to the tenth decimal place and float8 is precise up to 15 fractional digits, the problem does not apply. The manual:

On all currently supported platforms, the real type has a range of
around 1E-37 to 1E+37 with a precision of at least 6 decimal digits.
The double precision type has a range of around 1E-307 to 1E+308 with
a precision of at least 15 digits. Values that are too large or too
small will cause an error. Rounding might take place if the precision
of an input number is too high. Numbers too close to zero that are not
representable as distinct from zero will cause an underflow error.

So just:

SELECT round(avg(ratings.rating), 10)::float8

Note that we cast after rounding, as the variant of round() accepting a number of decimal places only works for numeric (due to the inexact nature of internal storage of floating point numbers).

---

Inside Postgres, you wouldn't worry too much about those trailing zeroes. The manual:

Numeric values are physically stored without any extra leading or
trailing zeroes. Thus, the declared precision and scale of a column
are maximums, not fixed allocations. (In this sense the numeric type
is more akin to varchar(n) than to char(n).) The actual storage
requirement is two bytes for each group of four decimal digits, plus
three to eight bytes overhead.

See:

• PostgreSQL adds trailing zeros to numeric

Inside Postgres, equality is established correctly:

SELECT numeric '4.50000000' = numeric '4.5'  -- true
SELECT jsonb '{"scents_id": 4.5}' = jsonb '{"scents_id": 4.5000}'  -- true

Your client throwing the error seems to compare text representations, which is subtly incorrect. So you may have to format like your client expects ...

How to convert any number into decimal value upto 2 decimal places?

Try FM9990.90:

postgres=# select c, to_char(c,'$FM9990.90') from t;
   c    | to_char  
--------+----------
  14.40 | $14.40
   7.70 | $7.70
 110.70 | $110.70
  17.28 | $17.28
  84.00 | $84.00
    0.1 | $0.10
   0.01 | $0.01
   1000 | $1000.00
      1 | $1.00
(9 rows)

---

Related Topics