PostgreSQL Question posted in
The official documentation can be found here.

How to calculate the fraction of rows fulfilling some criteria in Postgresql?

I would like to calculate the fraction of rows fulfilling some criteria. So something like this:

(SELECT COUNT(*) FROM table WHERE col_name > crit) / (SELECT COUNT(*) FROM table)

However, this creates a syntax error when I tried it.

How can I realize what I want?

Tags:

2

Answers


  1. 0

    The correct syntax would be:

    select (select count(*)::decimal from table where col_name > crit) / count(*)::decimal
from table

    But you can (mis)use the avg function too:

    select avg(case when col_name > crit then 1.0 else 0.0 end)
from table
    Login or Signup to reply.
  2. 0

    You are using perfectly valid syntax. It’s just incomplete, missing a select out front: demo

    SELECT (SELECT COUNT(*)::float FROM tbl WHERE col_name > .5) / (SELECT COUNT(*) FROM tbl);

    That makes your two parenthesized expressions uncorrelated scalar subqueries of the outer select. Note that you need the divident or the divisor to be numeric, real or float to get a fraction, otherwise the result of dividing integers from count(*) will be rounded.

    The proper (also quickest) way to do that is using aggregate filter clause:

    SELECT COUNT(*)FILTER(WHERE col_name > .5)/COUNT(*)::float FROM tbl;

    Picking float/double precision over numeric, you won’t have to round off trailing zeroes.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search