Nested Select? Count on a count

[Miguel]

I have a Students table which includes the fields FamilyCode and StudentKey. I need to be able to count the number of families that have one student, two students, three students and more than three students. It doesn't sound too hard, but I'm stuck. Any ideas?

 

[bigscouse]

Miguel, have you tries something like this?<br>
CREATE TABLE temp1 (<br>
FamilyCode=varchar,<br>
StudentCount=smallint );<br>
INSERT INTO temp1 (FamilyCode, StudentCount)<br>
SELECT DISTINCT FamilyCode,<br>
count(StudentCode)<br>
FROM sourcetable;<br>
SELECT FamilyCode,<br>
StudentCount<br>
FROM temp1<br>
WHERE StudentCount = 1;<br>
<br>
SELECT FamilyCode,<br>
StudentCount<br>
FROM temp1<br>
WHERE StudentCount = 2; <br>
<br>
ETC........<br>
<br>
DROP temp1;<br>
COMMIT;<br>
<br>
<br>
<p>Ged Jones<br><a href=mailto:gedejones@hotmail.com>gedejones@hotmail.com</a><br><a href= > </a><br>

 

[calahans]

Try - <br>
<br>
SELECT A.NUMSTD,COUNT(A.FAMILY) FROM (<br>
SELECT FAMILY, COUNT(STUDENTID) AS NUMSTD FROM<br>
STUDENTTABLE GROUP BY FAMILY) A<br>
GROUP BY A.NUMSTD<br>
<br>
<br>
This should work<br>
<br>
Cal<br>