title: How can I add "row position" to a query with a SUM(…) column from subquery?
body: <p>I am getting a weird error when I try to get SUM(…) column in a subquery and then get row "indexes":</p>
  
  <pre><code>WITH lb(displayName, val) AS (
      SELECT user.displayName, SUM(stats.weight) AS val FROM stats
      LEFT OUTER JOIN user ON user.id = stats.userId
      GROUP BY user.id
      ORDER BY val DESC
  )
  SELECT displayName, val, (SELECT COUNT(*) FROM lb b WHERE a.val &lt;= b.val) AS position FROM lb a
  </code></pre>
  
  <p>The error I get when I run it in <a href="https://github.com/sqlitebrowser/sqlitebrowser" rel="nofollow">SQLiteBrowser</a> is:
  <code>misuse of aggregate: SUM():</code> followed by the full text of the query.</p>
  
  <p>However, the query works fine if I either:</p>
  
  <ul>
  <li>remove the <code>, (SELECT COUNT(*) FROM lb b WHERE a.val &lt;= b.val) AS position</code> from the final SELECT;</li>
  <li>do not use SUM.</li>
  </ul>
  
  <p>(but I do need both)</p>
  
  <p><strong>Here is the (working) example (sqlfiddle), where the row index is missing (it also includes some test data):</strong> <a href="http://sqlfiddle.com/#!5/b1cc3/1/0" rel="nofollow">http://sqlfiddle.com/#!5/b1cc3/1/0</a></p>
  
  <p>How can I fix it, so it would work (that means, add row numbers to the result set)?</p>
  
  <p>I need these indexes because I'm going to add a WHERE clause after this query, and I need to know the order of the rows which were retained.</p>
  
  <p>For example, the wanted result for the test data in the fiddle above is:</p>
  
  <pre><code>| displayName |  val | position |
  |-------------|------|----------|
  |       name2 | 1256 |        1 |
  |       name4 |  133 |        2 |
  |       name5 |   62 |        3 |
  |       name3 |   43 |        4 |
  </code></pre>
  
  <p>(position could as well start with 0, it's not important)</p>
  
  <p>P.S. replacing the SQL implementation (SQLite) with something else is not an option (at least not right now)</p>