count number of rows based on another table in sql server

Question

I have two tables - one for staff and another for their children.

How can I group people on number of children?

staff table

pID|name|family|ETC|  
---- |-------- |-------|-------|    
123 |abc|ddd   | 134 | f |   
124 |dfv |eee   | 900 | d |     
126 |rgt |wwww|750|m|    

children table

pID|name|family|ETC|        
---- |-------- |-------|-------|    
123 |mah|ddd |2005/06/09|son |    
123 |dar|ddd |2013/11/01|girl|    
123 |sia|ddd |2003/01/01|girl|    
126 |naz|wwww|2007/25/01|girl|     

Expected table

pID|no|        
----- |- |    
123 |3|    
124 |0|    
126 |1|

Answer 1

Join from staff to children and grouping by pID will do the job for you:

select
    S.pID,
    count(C.name) as no
from staff as S
    left outer join childern as C on C.pID = S.pID
group by S.pID

Answer 2

Try this

select S.pID,
(select isnull(COUNT(*),0) from children C where C.pID=S.pID)
from Staff S

Answer 3

select s.pId
     , (select count(*) from [children] c where c.pId = s.pId)
  from [staff] s;

Replace [children] and [staff] with your actual table names.

Answer 4

SELECT staff.pid,count(*) FROM staff,children WHERE staff.pid=children.pid GROUP BY staff.pid;

This query will give you the resultant output.

Answer 5

If you want the result of all PiDs (Even if they 0 children) then use:

select
  staff.pID,count(children.name) as count
from staff
  left outer join childern on childern.pID = staff.pID
group by S.pID

(Same Answer as Andy Korneyev)

However, if you just want the children count (who have greater than 0) then use:

select
  staff.pID,count(children.name) as count
from staff
  inner join childern on childern.pID = staff.pID
group by S.pID

Hope this helps.