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|

Have you tried something yet?

Tim Biegeleisen
– Tim Biegeleisen

2017-03-15 08:02:34 +00:00
Commented Mar 15, 2017 at 8:02 — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 15, 2017 at 8:02

Andrey Korneyev · Accepted Answer · 2017-03-15 08:02:09Z

4

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

answered Mar 15, 2017 at 8:02

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jaleel Over a year ago

can get the number of staff based on Children Count? like 0 children 50 people, 1 child 60 people, etc?

Andrey Korneyev Over a year ago

@jaleel Why not? Just wrap the query into one more select grouping by no and counting pID - and you'll get exactly what you want.

Bilal Ayub · Accepted Answer · 2017-03-15 08:03:17Z

1

Try this

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

answered Mar 15, 2017 at 8:03

Bilal Ayub

1424 bronze badges

Comments

jurgenvo · Accepted Answer · 2017-03-15 08:03:32Z

0

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.

answered Mar 15, 2017 at 8:03

jurgenvo

2031 gold badge2 silver badges8 bronze badges

Comments

ed_me · Accepted Answer · 2017-03-15 08:17:03Z

0

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

This query will give you the resultant output.

edited Mar 15, 2017 at 8:17

ed_me

3,5282 gold badges26 silver badges36 bronze badges

answered Mar 15, 2017 at 8:08

Amandeep Singh

12 bronze badges

1 Comment

jarlh Over a year ago

Tip of today: Switch to modern, explicit JOIN syntax! Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed.

Bilal · Accepted Answer · 2017-03-15 08:33:49Z

0

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.

answered Mar 15, 2017 at 8:33

Bilal

551 silver badge8 bronze badges

Collectives™ on Stack Overflow

count number of rows based on another table in sql server

5 Answers 5

2 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related