Postgresql: Query to know which fraction of the values are larger/smaller

Question

I would like to query my database to know which fraction/percentage of the elements of a table are larger/smaller than a given value.

For instance, let's say I have a table shopping_list with the following schema:

   id   integer
 name   text
price   double precision

with contents:

id       name  price
 1     banana      1
 2       book     20
 3    chicken      5
 4  chocolate      3

I am now going to buy a new item with price 4, and I would like to know where this new item will be ranked in the shopping list. In this case the element will be greater than 50% of the elements.

I know I can run two queries and count the number of elements, e.g.:

-- returns = 4
SELECT COUNT(*)
FROM shopping_list;

-- returns = 2
SELECT COUNT(*)
FROM shopping_list
WHERE price > 4;

But I would like to do it with a single query to avoid post-processing the results.

Answer 1

I found the aggregate function PERCENT_RANK which does exactly what I wanted:

SELECT PERCENT_RANK(4) WITHIN GROUP (ORDER BY price)
FROM shopping_list;
-- returns 0.5