Link to home
Create AccountLog in
Avatar of javilmer

asked on

SQL QUESTION (SQL SERVER) : nb of occurences in a list


I have a table of personns (personnID, name) and a tables of commands (CommandRef, PersonnID), several commands being associated with one personn thru the PersonnID column.

So to see my commands and personns, I do :

select name, commandRef from personns left join commands on personns.personnID = commands.PersonnID

So I get something like :

Smith Ref-1
Smith Ref-2
Smith Ref-3
Taylor Ref-4
Johns Ref-5

I need to have the information that a name as several commands, so I need the listing to have a third column like :

Smith Ref-1 3
Smith Ref-2 3
Smith Ref-3 3
Taylor Ref-4 1
Johns Ref-5 1

Is there a "direct" way (without using a UDF function that counts because of performance problem) to get this third column in SQL ?

Any help greatly appreciated
Avatar of Farzad Akbarnejad
Farzad Akbarnejad
Flag of Iran, Islamic Republic of image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of javilmer


Thank you, this is perfect :)