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

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

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