Hi Steven!
>I want a list of courses with the number of students who pass and fail each one. The following is what I am working with, but am having no luck getting correct numbers for the nPass and nFail vars. Would anyone have a suggestion?>SELECT Courses.ctitle,Courses.tstartdate, ;
> Courses.csectnum, Courses.iid,;
> COUNT(students.cstatus = "PASS" ) AS nPass,;
> COUNT(students.cstatus = "FAIL" ) AS nFail;
> FROM regman!courses INNER JOIN regman!students ;
> ON Courses.iid = Students.icourseid;
> GROUP BY Courses.csectnum;
> ORDER BY Courses.tstartdate
I think that one way is you to do 2 select's, thus:
SELECT Courses.ctitle,Courses.tstartdate, ;
Courses.csectnum, Courses.iid,;
COUNT(students.cstatus) AS nPass,;
FROM regman!courses INNER JOIN regman!students ;
ON Courses.iid = Students.icourseid;
Where students.cstatus = "PASS" ;
GROUP BY Courses.csectnum;
ORDER BY Courses.tstartdate
SELECT Courses.ctitle,Courses.tstartdate, ;
Courses.csectnum, Courses.iid,;
COUNT(students.cstatus) AS nFail,;
FROM regman!courses INNER JOIN regman!students ;
ON Courses.iid = Students.icourseid;
Where students.cstatus = "FAIL" ;
GROUP BY Courses.csectnum;
ORDER BY Courses.tstartdateMaybe other way woulde you to do Sub-Select in you Select statement, but I think that is only possible in VFP8 or VFP9.
I hope this help you.
Erick
Força Sempre!
Strength Always!
