Counting Instances of Students in a Class

[Guest]

Hi,
i am trying to write a query which groups students to each module studied
and counts the number of students studying that module. I.e. there may be two
students studying a course which contains five modules there will be five
names and the answer should be two. Can you help me please?

Ta
iain

 

[Guest]

Why not just total on students and forget about modules?

 

[Guest]

Sounds too simple. The problem is I have an instance of 5 studentIDs taking
four modules but there are only two students.

 

[Guest]

Test data --
StudentID Module
1 A
1 B
1 C
1 D
2 A
Totals query --
SELECT Iain.StudentID
FROM Iain
GROUP BY Iain.StudentID;

Results --
StudentID
1
2

 

[Guest]

Hi,
I am using a query to find the names of students studying in a selected week
and the output gives me a list of student ID's. The problem is I do not knopw
how to count the individuals concerned. I am sorry if I am being thick here.

IAin

 

[John Spencer]

IT would probably help if you posted the SQL of your present query.

Do you want to count the students over all programs for the period

SELECT Count(StudentID)
FROM
(SELECT Distinct StudentID
FROM Modules
WHERE StudyPeriod Between #2007-02-01# and #2007-02-06#)


Or use a subquery in the where clause assuming you have a students table

SELECT Count(StudentID)
FROM Students
WHERE StudentID in
(SELECT StudentID
FROM Modules
WHERE StudyPeriod Between #2007-02-01# and #2007-02-06#)





Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================