>I have a problem with my SQL statement. I just can't get the results I want. Here is my statement
>
>select course.courseid, count(studentcourse.courseid) as stdcount, instructor.instructorid;
> from course left join studentcourse on course.courseid = studentcourse.courseid;
> left join instructor on studentcourse.instructorid = instructor.instructorid;
> into cursor tempcourse group by course.courseid
>
>I want to count the number of students in a course but there is sometimes two instructors teaching a course so I get the course only once with the first instructorid it finds related to that course and the number of students. I know this is because I am grouping by course.courseid. But if I get rid of that I get only one course with the total number of students. What else could I try?
>
>
>This is what I get
>
>courseid | stdcount | instructorid
>
>CJP165D01 | 4 | CJ08
>
>
>This is what I want
>
>courseid | stdcount | instructorid
>
>CJP165D01 1 CJ08
>CJP165D01 3 CJ06
>
>
>Thanks in advance,
>
>Tyler

Tyler, you may try to call UDF from your SELECT to get the count.
It may look like:

SELECT course.courseid, student_count(studentcourse.courseid) as stdcount, instructor.instructorid;
FROM course ;
LEFT JOIN studentcourse ;
ON course.courseid = studentcourse.courseid;
LEFT JOIN instructor on studentcourse.instructorid = instructor.instructorid;
INTO cursor tempcourse group by course.courseid


****
FUNCTION student_count
LPARAMETER tcCourseID
LOCAL laCount[1]
laCount[1] = 0

SELECT COUNT(studentcourse.courseid) ;
FROM course ;
WHERE studentcourse.courseid = tcCourseID ;
into array laCount

RETURN laCount[1]
ENDFUNC


Nick