PostgreSQL RANK() function over aggregated column
<p>I'm constructing quite complex query, where I try to load users with their aggregated points altogether with their rank. I found the RANK() function that could help me to achieve this but can't get it working.</p>

<p>Here's the query that is working without RANK:</p>

<pre><code>SELECT users.*, SUM(received_points.count) AS pts 
FROM users 
LEFT JOIN received_points ON received_points.user_id = users.id AND ...other joining conditions... 
GROUP BY users.id 
ORDER BY pts DESC NULLS LAST
</code></pre>

<p>Now I would like to select also the rank - but this way using RANK function it's not working:</p>

<pre><code>SELECT users.*, SUM(received_points.count) AS pts, 
    RANK() OVER (ORDER BY pts DESC NULLS LAST) AS position
FROM users 
LEFT JOIN received_points ON received_points.user_id = users.id AND ...other joining conditions... 
GROUP BY users.id 
ORDER BY pts DESC NULLS LAST
</code></pre>

<p>It tells: <code>PG::UndefinedColumn: ERROR: column "pts" does not exist</code></p>

<p>I guess I get whole concept of window functions wrong. How can I select the rank of user sorted by aggregated value like <code>pts</code> in example above? </p>

<p><em>I know I can assign ranks manually afterwards but what if I want to also filter the rows according to users.name in query and still get user's rank in general (not-filtered) leaderboard...? Dont know if I'm clear...</em></p>