Can't figure out SQL Query 1

[lovejaeeun]

Hi,

I have a FAMILY table and a CHILD table with a one to many remlationship where a FAMILY can have multiple CHILD.

The FAMILY ID is the FK in the CHILD table.

1)
I want to write a query that would display the following as a result: (how many familes have 'x' amount of children)

# children families
0 15
1 98
2 253
3 194
4 110
etc.

2)
I also want to write a query to find out the average number of children a family has.


The closest I have got is:
SELECT Count(*)
FROM CHILD
GROUP BY FamilyID
HAVING Count(*) = 1;

*And then I have to repeat this for 2 children, 3 children, 4 etc..

Thank you!

 

[DreXor]

#1
SELECT Count(FamilyID) As [Total]
FROM CHILD
GROUP BY FamilyID
HAVING Count(FamilyID) >= 0


#2
would be a sum(children) then you divide that by count(familyID) dont know exactly how this would be worded for you

 

[rac2]

A query to tell how many children each family has.

SELECT FamilyID, COUNT(*) AS "CountRugRats"
FROM Child
GROUP BY FamilyID

Save that and name it FamilySize.

A query to tell how many families of each size.

SELECT CountRugRats, COUNT(*) AS "CountFamilies"
FROM FamilySize
GROUP BY CountRugRats

Or you might try putting it all in one query

SELECT CountRugRats AS "# Children",
       COUNT(*) AS "Families"
FROM (
      SELECT FamilyID, COUNT(*) AS "CountRugRats"
      FROM Child
      GROUP BY FamilyID
     )
GROUP BY CountRugRats

 

[rac2]

SELECT AVG(CountRugRats)
FROM FamilySize

 

[lovejaeeun]

rac2,

Thanks! I was looking for the 'single-query' solution (which you also provided.) Works exactly like I wanted!

lovejaeeun