How to use partition on a join to get a count

Question

I'm confused on how to get a count without using group by on a join

I know I can get the desired results using group by, but the table joins are long and lots of selected headers with case statement so I was hoping to avoid that

I'm sure I've seen this done before using partition over but can't find a good example using it on a join. Maybe it's not possible!?

I've tried

select
    p.FirstName,
    p.Surname,
    count(pr.RelativePersonId) over (partition by pr.RelativePersonId) as [RelativesOnRecord]
from People p
left join PersonRelatives pr
    on p.PersonId = pr.PersonId

For my tables:

People

PersonId | FirstName | Surname
   1          Jim       Bo
   2         Harry      Bo
   3         Strong     Bo

PersonRelatives

Id | PersonId | RelativePersonId
 1      1              2
 2      1              3

Where I'm trying to get

PersonId | FirstName | Surname | RelativesOnRecord
    1         Jim         Bo          2

I also tried joining with a SELECT TOP 1 but that just gives me the one result so one count. Is this even possible without group by?

Answer 1

It seems you are partitioning by the wrong column - you want to have the number of relatives for each person from People, right ? Use

count(pr.RelativePersonId) over (partition by pr.PersonId) as [RelativesOnRecord]