SQL dense ranking function

Question

I have a table called employees, with 'employee_id' and 'bonuses' as columns. The employee_id's are repeated because they have multiple bonuses of different amounts, and I want to rank them by their total bonus amount.

So, I was wondering if this would be the correct way to rank the employees by their total bonus amount descending:

SELECT employee_id, SUM(bonuses) AS total_bonus, DENSE_RANK(ORDER BY total_bonus DESC) AS bonus_rank
FROM employees
GROUP BY employee_id;

My desired output is a table with the employee_id only listed once, the corresponding total bonus amount the employee received, and the rank of bonuses starting from rank #1 with the highest total bonus amount.

Provide some sample data of your case and the expected result from that sample. — Viki888, Jan 23 '17 at 04:27

score 0 · Answer 1 · answered Jan 23 '17 at 04:38

0

You can just use the computed Bonus value in the ORDER BY clause - no need to bother with dense ranks.

SELECT employee_id, SUM(bonuses) AS total_bonus
FROM employees
GROUP BY employee_id
ORDER BY SUM(bonuses) DESC

answered Jan 23 '17 at 04:38

Zac Faragher

963
13
26

Tim Biegeleisen · Answer 2 · 2017-01-23T04:50:10.210

I don't see anything wrong with your query except that your are attempting to use an alias total_bonus in the DENSE_RANK() function at the same level of a SELECT. This can't be done, AFAIK, because the alias won't yet be avaiable. Instead, just use the SUM() term again:

SELECT employee_id,
       SUM(bonuses) AS total_bonus,
       DENSE_RANK(ORDER BY SUM(bonuses) DESC) AS bonus_rank
FROM employees
GROUP BY employee_id;

SQL dense ranking function

2 Answers2