Home > other >  Calculate dense rank by sum
Calculate dense rank by sum

Time:03-06

Sample data:

student marks subject
stud1 100 sub1
stud1 400 sub2
stud1 500 sub3
stud2 200 sub1
stud2 700 sub2
stud2 800 sub3
stud2 900 sub4
stud3 300 sub1
stud3 600 sub2
stud4 1000 sub1

Trying to partition by student and order by sum of marks like below using dense_rank(). There are multiple other columns and since the data is big, trying to avoid any joins here.

Expected output:

student marks subject ds_rnk
stud1 100 sub1 2
stud1 400 sub2 2
stud1 500 sub3 2
stud2 200 sub1 1
stud2 700 sub2 1
stud2 800 sub3 1
stud2 900 sub4 1
stud3 300 sub1 3
stud3 600 sub2 3
stud4 1000 sub1 2

Thanks in advance!

CodePudding user response:

You can calculate sum and dense_rank in two stages:

with cte as (
    select t.*, sum(marks) over (partition by student) as sum_marks
    from t
)
select cte.*, dense_rank() over (order by sum_marks desc) as dense_rankk
from cte
  • Related