In Postgres, how can I calculate cume_dist (relative rank) over the entire table, but only for a single row?

I have a table with the following schema:

CREATE TABLE foo (
  data  TEXT   NOT NULL,
  count BIGINT NOT NULL DEFAULT 1,
  PRIMARY KEY (data)
);

I'd like to get the relative rank of a single entry, based on count, as it relates to the entire table, but I'm not sure how to structure the OVER clause in order to do this. Running the following gives me a rank of 1, so I'm assuming the OVER clause is getting limited to a single row, rather than all the rows in the table, as dictated by the WHERE clause.

SELECT cume_dist() OVER(ORDER BY f.count ASC) AS rank
FROM foo AS f
  WHERE f.data = 'bar';

rank 
------
    1
(1 row)

Thoughts?

You need to first apply the function in the whole table and then restrict the results with WHERE:

SELECT r.rank
FROM
  ( SELECT f.data, cume_dist() OVER (ORDER BY f.count ASC) AS rank
    FROM foo AS f
  ) AS r
WHERE r.data = 'bar';

Alternatively, you could calculate the function with 2 subqueries:

SELECT
    ( SELECT COUNT(*) 
      FROM foo 
      WHERE count <= (SELECT count FROM foo WHERE data = 'bar')
    ) 
  * 1.0 /
    ( SELECT COUNT(*) FROM foo )
  AS rank ;

or with the new FILTER syntax:

SELECT COUNT(*) FILTER 
                (WHERE f.count <= (SELECT count FROM foo WHERE data = 'bar')) 
       * 1.0 / COUNT(*) AS rank
FROM foo AS f ;

StackExchange Database Administrators Q#138657, answer score: 4