How to rank and count the data in sql

Transact-SQL
1

Hi,

I am having data like below and i want to rank and take the count of the rows

EMPID DOMAIN FIRSTNAME LASTNAME LANID STATUS
5102 Windows James Johnson JJohnson A
1023 Windows James Johnson JJohnson T
8456 Windows Michael Smith MSmith A
6597 UNIX Michael Smith MSmith1 A
3549 Windows Maria Garcia MGarcia A
2567 Windows Maria Garcia MGarcia T
2111 UNIX Maria Garcia MGarcia T

A - Active T- Terminated

I want the above data to be ranked and counted by using the first and last name and order by first using DOMAIN and then by STATUS like below, can you let me know how to query the data to get the below result

EMPID DOMAIN FIRSTNAME LASTNAME LANID STATUS RANK COUNT
5102 Windows James Johnson JJohnson A 1 1
1023 Windows James Johnson JJohnson T 2 1
8456 Windows Michael Smith MSmith A 1 2
6597 UNIX Michael Smith MSmith1 A 2 2
3549 Windows Maria Garcia MGarcia A 1 1
2567 Windows Maria Garcia MGarcia T 2 1
2111 UNIX Maria Garcia MGarcia T 2 1

Thanks in advance

2

In your sample data, Maria Garcia has 3 rows, but the rank goes 1,2,2. Why doesn't it go 1,2,3?

Also your description of sorting doesn't comply with what you show. Please explain.

4

What you just wrote didn't make sence to me, sorry.

I think you can start with this, and modify it to you needs:

select *
      ,row_number() over(partition by firstname,lastname order by status) as [rank]
      ,sum(case when [status]='A' then 1 else 0 end) over(partition by firstname,lastname) as [count]
  from yourtable
 order by firstname,lastname,[rank]
;

I belive lanid is unique for firstname/lastname, so this will do the same as above:

select *
      ,row_number() over(partition by lanid order by status) as [rank]
      ,sum(case when [status]='A' then 1 else 0 end) over(partition by lanid) as [count]
  from yourtable
 order by firstname,lastname,[rank]
;
1 Like