How to round an average to 2 decimal places in PostgreSQL

Question

I am using PostgreSQL via the Ruby gem  sequel    I m trying to round to two decimal places   Here s my code   SELECT ROUND AVG some column  2      FROM table   I get the following error    PG  Error  ERROR   function round double precision  integer  does  not exist  Sequel  DatabaseError    I get no error when I run the following code   SELECT ROUND AVG some column   FROM table   Does anyone know what I am doing wrong

User · Accepted Answer

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  gt  SELECT round  float8  3 1415927   2    ERROR   function round double precision  integer  does not exist  regress  gt   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  gt  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  gt  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

User · Answer

you can use the function below    SELECT TRUNC 14 568 2     the result will show    14 56   you can also cast your variable to the desire type     SELECT TRUNC YOUR VAR  numeric 2

User · Answer

Error function round double precision  integer  does not exist   Solution  You need to addtype cast then it will work  Ex   round extract second from job end time t   integer 0

User · Answer

Try with this   SELECT to char  2 3  float   FM999999990 00    -- RESULT  0 67   Or simply   SELECT round  2 3  DECIMAL  2   TEXT -- RESULT  0 67

User · Answer

According to Bryan s response you can do this to limit decimals in a query  I convert from km h to m s and display it in dygraphs but when I did it in dygraphs it looked weird  Looks fine when doing the calculation in the query instead  This is on postgresql 9 5 1   select date  wind speed 3 6   numeric 7 1  from readings

User · Answer

Try casting your column to a numeric like   SELECT ROUND cast some column as numeric  2  FROM table

User · Answer

Try also the old syntax for casting  SELECT ROUND AVG some column   numeric 2      FROM table   works with any version of PostgreSQL  There are a lack of overloads in some PostgreSQL functions  why         I think  quot it is a lack quot       but  CraigRinger   Catcall and the PostgreSQL team agree about  quot pg s historic rationale quot   PS  another point about rounding is accuracy  check  IanKenney s answer   Overloading as casting strategy You can overload the ROUND function with   CREATE FUNCTION ROUND float int  RETURNS NUMERIC AS        SELECT ROUND  1  numeric  2       language SQL IMMUTABLE   Now your instruction will works fine  try  after function creation   SELECT round 1 3  4   -- 0 3333 numeric  but it returns a NUMERIC type    To preserve the first commom-usage overload  we can return a FLOAT type when a TEXT parameter is offered   CREATE FUNCTION ROUND float  text  int DEFAULT 0    RETURNS FLOAT AS        SELECT CASE WHEN  2  dec                  THEN ROUND  1  numeric  3   float                 --     WHEN  2  hex  THEN     WHEN  2  bin  THEN    complete                  ELSE  NaN   float  -- like an error message              END      language SQL IMMUTABLE   Try  SELECT round 1 3   dec  4     -- 0 3333 float   SELECT round 2 8 1 3   dec  1   -- 3 1 float   SELECT round 2 8 1 3   dec   text   -- need to cast string  pg bug    PS  checking  df round after overloadings  will show something like    Schema        Name   Result data type   Argument data types  ------------ ------- ------------------ ----------------------------  myschema     round   double precision   double precision  text  int  myschema     round   numeric            double precision  int  pg catalog   round   double precision   double precision              pg catalog   round   numeric            numeric     pg catalog   round   numeric            numeric  int            The pg catalog functions are the default ones  see manual of build-in math functions

User · Answer

SELECT ROUND SUM amount   numeric  2  AS total amount FROM transactions  Gives  200234 08

[sql] How to round an average to 2 decimal places in PostgreSQL?

Examples related to sql

Examples related to ruby

Examples related to postgresql

Examples related to sequel