Calculations with COUNT()

Question

I have a dataset I can group and want to know the percentage of rows in each group.

This seems to nearly works, except that it returns 0 for every group because of imho missing typecast

SELECT COUNT(*) / (SELECT COUNT(name) 
                    FROM x 
                    WHERE d = '0') 
  FROM x, y 
  WHERE x.a = y.a AND x.b = '0' 
  GROUP BY y.c 
  ORDER BY y.c ASC

How do I correctly cast this?

Answer 1

You should be able to do

select 1.0 * count(*) / .....

Answer 2

Rather than multiplying by 1.0, you could just cast. That seems cleaner and clearer to me. For one thing, it makes clear what data type you want to use. You may be happy enough with the precision of float4 or float8 approximations, rather than paying the extra cost for the exact numeric calculations.

SELECT COUNT(*)::float / (SELECT COUNT(name) 
                           FROM x 
                           WHERE d = '0')::float
  FROM x, y 
  WHERE x.a = y.a AND x.b = '0' 
  GROUP BY y.c 
  ORDER BY y.c ASC

test=# select 1.0 * 5 / 10;
        ?column?        
------------------------
 0.50000000000000000000
(1 row)

test=# select pg_typeof(1.0 * 5 / 10);
 pg_typeof 
-----------
 numeric
(1 row)

test=# select 5::float / 10::float;
 ?column? 
----------
      0.5
(1 row)

test=# select pg_typeof(5::float / 10::float);
    pg_typeof     
------------------
 double precision
(1 row)

Answer 3

If you convert to float your problem might be solved:

SELECT convert(double, COUNT(*)) / (SELECT convert(double, COUNT(name)) 
                    FROM x 
                    WHERE d = '0') 
  FROM x, y 
  WHERE x.a = y.a AND x.b = '0' 
  GROUP BY y.c 
  ORDER BY y.c ASC