Select a programmed calculated column to round to two 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 to 2 decimal values

my original table is something like this

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

I came up with this query, but it seems like it returns the value to me as a whole.

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 the conclusion to this as

a b c_avg
1 2 0
2 3 0

I need something like this

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

My Postgresql version on amazon redshift - PostgreSQL 8.0.2

There are a few things I do 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 a value of 0, not 0. *

thanks

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

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

meta :: float , .

Answer 1

postgresql ,

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

meta :: float , .

Answer 2

- float, :

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

Answer 3

Postgres , :

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

, :

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

Answer 4

:

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

:

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

Answer 5

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