title: PostgreSQL Window Function "column must appear in the GROUP BY clause"
body: <p>I'm trying to get a leaderboard of summed user scores from a list of user score entries. A single user can have more than one entry in this table.</p>
  
  <p>I have the following table:</p>
  
  <pre><code>rewards
  =======
  user_id | amount
  </code></pre>
  
  <p>I want to add up all of the <code>amount</code> values for given users and then rank them on a global leaderboard. Here's the query I'm trying to run:</p>
  
  <p><code>SELECT user_id, SUM(amount) AS score, rank() OVER (PARTITION BY user_id) FROM rewards;</code></p>
  
  <p>I'm getting the following error:</p>
  
  <pre><code>ERROR:  column "rewards.user_id" must appear in the GROUP BY clause or be used in an aggregate function
  LINE 1: SELECT user_id, SUM(amount) AS score, rank() OVER (PARTITION...
  </code></pre>
  
  <p>Isn't <code>user_id</code> already in an "aggregate function" because I'm trying to partition on it? The PostgreSQL manual shows the following entry which I feel is a direct parallel of mine, so I'm not sure why mine's not working:</p>
  
  <p><code>SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;</code></p>
  
  <p>They're not grouping by <code>depname</code>, so how come theirs works?</p>
  
  <p>For example, for the following data:</p>
  
  <pre><code>user_id | score
  ===============
  1 | 2
  1 | 3
  2 | 5
  3 | 1
  </code></pre>
  
  <p>I would expect the following output (I have made a "tie" between users 1 and 2):</p>
  
  <pre><code>user_id | SUM(score) | rank
  ===========================
  1 | 5 | 1
  2 | 5 | 1
  3 | 1 | 3
  </code></pre>
  
  <p>So user 1 has a total score of 5 and is ranked #1, user 2 is tied with a score of 5 and thus is also rank #1, and user 3 is ranked #3 with a score of 1.</p>