CODEWITHSUNDEEP
postgresql
aardvarkk
aardvarkk

Reputation: 15996

PostgreSQL Window Function "column must appear in the GROUP BY clause"

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.

Upvotes: 1

Views: 2720

Answers (3)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

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

Upvotes: 5

Mark
Mark

Reputation: 1181

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

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

If you have 

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

You need 

    GROUP BY user_id

Upvotes: 1

Related Questions