Postgres sql round to 2 decimal places

Question

I am trying to round my division sum results to 2 decimal places in Postgres SQL. I have tried the below, but it rounds them to whole numbers.

round((total_sales / total_customers)::numeric,2) as SPC,

round((total_sales / total_orders)::numeric,2) as AOV

How can I round the results to 2 decimal places please?

Kind Regards, JB78

Answer 1

Assuming total_sales and total_customers are integer columns, the expression total_sales / total_orders yields an integer.

You need to cast at least one of them before you can round them, e.g.: total_sales / total_orders::numeric to get a decimal result from the division:

round(total_sales / total_orders::numeric, 2) as SPC,
round(total_sales / total_orders::numeric, 2) as AOV

Example:

create table data
(
   total_sales integer,
   total_customers integer, 
   total_orders integer
);

insert into data values (97, 12, 7), (5000, 20, 30);

select total_sales / total_orders as int_result,
       total_sales / total_orders::numeric as numeric_result,
       round(total_sales / total_orders::numeric, 2) as SPC,
       round(total_sales / total_orders::numeric, 2) as AOV
from data;

returns:

int_result | numeric_result       | spc    | aov   
-----------+----------------------+--------+-------
        13 |  13.8571428571428571 |  13.86 |  13.86
       166 | 166.6666666666666667 | 166.67 | 166.67