SQL query count from instances

sql, sql-server

Solution

SELECT  busId, COUNT(*)
FROM    (
        SELECT  busId, studentId,
                ROW_NUMBER() OVER (PARTITION BY studentId ORDER BY id DESC) rn
        FROM    busSignupInstance
        ) q
WHERE   rn = 1
GROUP BY
        busId

Problem

I have a table contains bus and student sign up information: ``` CREATE TABLE [dbo].[BusSignupInstance]( [Id] [int] IDENTITY(1,1) NOT NULL, [StudentId] [int] NOT NULL, [BusId] [int] NOT NULL ) ON [PRIMARY] ``` Here are some sample data: ``` Id StudentId BusId 1 1 1 2 2 1 3 3 1 4 1 2 5 4 2 6 1 3 7 5 3 ``` I want the total number by bus ID. In this case will be: ``` BusId Num of Students 1 2 (student id 2, 3) 2 1 (student id 4) 3 2 (student id 1, 5) ``` The result is based on the order of sign up, if student 1 sign up three times, only the last time is effective.

Original source