Select query custom calculated column to round to 2 decimal places

Question

I have a table with some columns a,b, i need to add a custom column c_avg which will have the value c_avg = a*100/b upto 2 decimal values

my original table is some thing like this

id a b
1  1 2
2  2 3
3  2 0

I have come up with this query but seems as this returns me value as integer.

select round( CAST((CASE WHEN b=0 THEN '0.00'
        ELSE round(((a*100)/b),2)
   END ) as numeric) , 2) as c_avg
from table_name

i get output to this as

a b c_avg
1 2 0
2 3 0

i need some thing like this

a b c_avg
1 2 0.50
2 3 0.66
2 0 0

My Postgresql version on amazon redshift is PostgreSQL 8.0.2

There is also a few things I'm doing with this table

select sum(a) as aa, sum(b) as bb, groub_by_column
round( CAST((CASE WHEN sum(b)=0 THEN '0.00'
    ELSE round(((sum(a)*100)/sum(b)),2)
    END ) as numeric) , 2) as c_avg
from table group by groub_by_column

This returns me value to 0 and not to 0.*

Thanks

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

Guaiacol

Answer 1

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

Guaiacol

Answer 2

While working on integers, the result is also an integer - multiply by a float value to get a float result:

SELECT
    sum(a) as aa,
    sum(b) as bb,
    group_by_column,
    CASE
        WHEN 0 = sum(b) THEN 0.0
        ELSE ROUND(100.0 * sum(b) / sum(b), 2)
    END AS c_avg
FROM table_name
GROUP BY group_by_column

Conchaconchie

Please check the eddited section – Guaiacol

@rohitmb, I've expanded my solution to include also the GROUP BY part – Conchaconchie

Answer 3

Postgres does integer division, so try this version of your query:

select round((CASE WHEN b=0 THEN 0.00
                   ELSE a*100.0)/b
              END), 2) as c_avg
from table_name;

You can also do this by converting the result to a decimal:

select cast((case when b = 1 then 0 else a*100.0/b end) as decimal(5, 2)) as c_avg
from table_name;

Occasion

hey, not known to this, what does decimal(5, 2) do in this – Guaiacol

Please check other details added – Guaiacol

@Guaiacol . . . decimal() is a fixed width numeric type. It is an alternative to round() and part of the SQL standard. – Occasion

Answer 4

Try with this:

SELECT to_char (1/2::FLOAT, 'FM999999990.00');
--- RESULT: 0.50

Or simply:

SELECT round (2/3::DECIMAL, 2)::TEXT
--- RESULT: 0.67

Zasuwa

Answer 5

This is kind of an old question, but I found that casting to DECIMAL(10, 2) might be the best/easiest way to round to 2 decimal places in redshift. None of the other answers worked for me.

SELECT 10.4494685::DECIMAL(10,2)
-- OUTPUT 10.45

Cartercarteret

Answer 6

you need cast

select round( (2/3), 2) produces 0
select round( (2/3::numeric), 2) produces 0.67

Edmundoedmunds

select round( (2/3), 2) produces 0 select round( (2/3::numeric), 2) produces 1 – Zasuwa

@Zasuwa you are wrong. I testing that and select round( (2/3::numeric), 2) produces 0.67 – Edmundoedmunds

depends on the version of postgresql, I auditioned with 4 different versions – Zasuwa

The question is on postgres 8.0.2. see more in postgresql.org/docs/8.0/static/functions-math.html – Edmundoedmunds