1

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.

I have the following table:

rewards
=======
user_id | amount

I want to add up all of the amount values for given users and then rank them on a global leaderboard. Here's the query I'm trying to run:

SELECT user_id, SUM(amount) AS score, rank() OVER (PARTITION BY user_id) FROM rewards;

I'm getting the following error:

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...

Isn't user_id 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:

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

They're not grouping by depname, so how come theirs works?

For example, for the following data:

user_id | score
===============
1 | 2
1 | 3
2 | 5
3 | 1

I would expect the following output (I have made a "tie" between users 1 and 2):

user_id | SUM(score) | rank
===========================
1 | 5 | 1
2 | 5 | 1
3 | 1 | 3

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.

Improve this question
2
  • what result you want? Commented Mar 9, 2016 at 21:08
  • @JuanCarlosOropeza Added a bit more detail. Commented Mar 9, 2016 at 21:13

3 Answers 3

Reset to default
5

You need to GROUP BY user_id since it's not being aggregated. Then you can rank by SUM(score) descending as you want;

SQL Fiddle Demo

SELECT user_id, SUM(score), RANK() OVER (ORDER BY SUM(score) DESC)
FROM rewards 
GROUP BY user_id;

 user_id | sum | rank
---------+-----+------
       1 |   5 |    1
       2 |   5 |    1
       3 |   1 |    3
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

This looks like the simplest and most direct approach. I think I need to improve my understanding of exactly what "PARTITION BY" does. Thanks for your help!
PARTITION BY works as a GROUP BY for each row. In this case you need GROUP BY because you start with 4 rows and will end with 3. In the question case if you remove the SUM() the windows function will return all 4 rows
2

There is a difference between window functions and aggregate functions. Some functions can be used both as a window function and an aggregate function, which can cause confusion. Window functions can be recognized by the OVER clause in the query.

The query in your case then becomes, split in doing first an aggregate on user_id followed by a window function on the total_amount.

SELECT user_id, total_amount, RANK() OVER (ORDER BY total_amount DESC) FROM ( SELECT user_id, SUM(amount) total_amount FROM table GROUP BY user_id ) q ORDER BY total_amount DESC

Improve this answer

Comments

1

If you have 

    SELECT user_id, SUM(amount) ....
                   ^^^
                   agreagted function (not window function)
    ....
    FROM .....

You need 

    GROUP BY user_id
Improve this answer

2 Comments

If I group by user_id, they're all ranked 1. The partition is effectively grouping them, right? Am I not understanding how that's supposed to work?
Sorry, I just answer what is wrong. I m testing the right way to do it now :P

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.