Count child records

[leodippolito]

I am using JET (OleDB) within an ASP.NET application.

Suppose I have these tables:

Student
- id
- name
- collegeId

College
- id
- name

In other words, a college can have 0 - N students.

I need to select all colleges (id and name) and also the number (count)
of students associated with that college.

Is this possible with 1 query?

I appreciate any help.

 

[Marshall Barton]

I am using JET (OleDB) within an ASP.NET application.

Suppose I have these tables:

Student
- id
- name
- collegeId

College
- id
- name

In other words, a college can have 0 - N students.

I need to select all colleges (id and name) and also the number (count)
of students associated with that college.

Is this possible with 1 query?

SELECT College.[name],
Count(Student.id) As Enrolled
FROM College INNER JOIN Student
ON College .od = Student.id
GROUP BY College.[name]
ORDER BY College.[name]

 

[John Spencer]

Marshall,

Pardon me, but shouldn't one use a LEFT JOIN since the College (in theory)
could have no students (at least in the database).

SELECT College.ID
, College.[name]
, Count(Student.id) As Enrolled
FROM College LEFT JOIN Student
ON College.id = Student.CollegeID
GROUP BY College.ID, College.[name]
ORDER BY College.[name]

John

I am using JET (OleDB) within an ASP.NET application.

Suppose I have these tables:

Student
- id
- name
- collegeId

College
- id
- name

In other words, a college can have 0 - N students.

I need to select all colleges (id and name) and also the number (count)
of students associated with that college.

Is this possible with 1 query?

SELECT College.[name],
Count(Student.id) As Enrolled
FROM College INNER JOIN Student
ON College .od = Student.id
GROUP BY College.[name]
ORDER BY College.[name]

 

[Marshall Barton]

Sure thing John.

But then I would have to wonder what sort of college has no
students and why they should be counted?
;-)
--
Marsh
MVP [MS Access]

Pardon me, but shouldn't one use a LEFT JOIN since the College (in theory)
could have no students (at least in the database).

SELECT College.ID
, College.[name]
, Count(Student.id) As Enrolled
FROM College LEFT JOIN Student
ON College.id = Student.CollegeID
GROUP BY College.ID, College.[name]
ORDER BY College.[name]

John

I am using JET (OleDB) within an ASP.NET application.

Suppose I have these tables:

Student
- id
- name
- collegeId

College
- id
- name

In other words, a college can have 0 - N students.

I need to select all colleges (id and name) and also the number (count)
of students associated with that college.

Is this possible with 1 query?

SELECT College.[name],
Count(Student.id) As Enrolled
FROM College INNER JOIN Student
ON College .od = Student.id
GROUP BY College.[name]
ORDER BY College.[name]