CODEWITHSUNDEEP
sql-server
ThoureaulyRekt
ThoureaulyRekt

Reputation: 97

Turn queried sum into ranking column

I'm an SQL novice still trying to understand syntax differences between MySQL and SQL Server. I recently solved this problem on MySQL but on SQL Server it looks completely different, and I can't get it to work here.

I want to build a ranking column into my query. I have a table, EnrollmentX, with two columns, a unique StudentID and a GroupId (with groups 1:3). I need to simultaneously count the number of students in each of these three groups and then rank the the groups by number of students.

The end result should look like this:

GroupId / StudentCnt / Rank
   2    /     20     /   1

...and so on for all three groups. But while most of my syntax has worked between My SQL and SQL Server, this raises all sorts of problems with SQL Server, especially around calling out variables differently than I'm used to.

How do I solve this in SQL Server?

Upvotes: 0

Views: 37

Answers (2)

Mazhar
Mazhar

Reputation: 3837

You can use RANK or DENSE_RANK depending on your requirements

Note how the next number in the Rank columns differ between the two when totals match

DECLARE @t TABLE ( StudentId int, GroupId INT)
INSERT INTO @t 
VALUES(1, 1),(2, 2),(3, 2),(4, 2),(5, 3),(6, 3),(7, 3),(8, 3),(9, 2)

SELECT 
     GroupId
    ,StudentCnt = COUNT(StudentId)
    ,[Rank] = RANK()OVER(ORDER BY COUNT(StudentId) DESC)
    ,[DENSE_RANK] = DENSE_RANK()OVER(ORDER BY COUNT(StudentId) DESC)
FROM @t
GROUP BY
    GroupId

Output

GroupId StudentCnt  Rank    DENSE_RANK
2       4           1       1
3       4           1       1
1       1           3       2   --<< this row

Upvotes: 1

SqlKindaGuy
SqlKindaGuy

Reputation: 3591

You can do it like this:

However, you dont write what if the group has same amount of studentcnt? Should they be ranked equal then?

create table #test
(
groupid int, students int)

insert into #test
values
(1,10),
(1,14),
(2,10),
(2,5),
(2,5),
(4,40),
(5,25)

Select groupid,studentcnt ,rank() over(order by studentcnt) as rank from (
select groupid,sum(students) as StudentCnt from #test

group by groupid
)x

drop table #test

Result

enter image description here

Upvotes: 1

Related Questions