troubleshooting Question

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

Avatar of javilmer
javilmer asked on
Microsoft SQL Server
2 Comments1 Solution234 ViewsLast Modified:

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
Farzad Akbarnejad

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros