Geek asks and answers

Calculations using COUNT ()

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

It seems like it almost worked, except that it returns 0 for each group due to the lack of a dashing type

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 to do it right?

+5
source share
3 answers

Instead of multiplying by 1.0, you can just quit. It seems to me more pure and understandable. First, it clearly indicates which type of data you want to use. You can be quite happy with the accuracy of the approximations float4or float8, rather than pay the extra cost for accurate calculations numeric.

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)
+3

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

If you convert to float, your problem can 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
+1
source

More articles:


All Articles