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?
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)
You should be able to do
select 1.0 * count(*) / .....
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
