I have a DB with two tables
Extracts are as follows:
stuID Name Surname Gender Schoolname etc. (irrelevant to q)
1001 John Dawson M PretoriaHS
1002 Peter Zondi M Hillcrest
(only one entry here for each stuID)
stuID course mark symbol seniorCourse
1001 ECO1 54 3 N
1001 MAM3 75 1+ Y
1001 PHY2 52 3 Y
1002 PHY2 45 F Y
(multiple entries for each stuID)
etc etc. There are a few hundred records in each.
The following are two questions I have been battling with for over 1.5 hrs now!
Which courses have the lowest number of fails (symbol = 'f'), sounds easy, but can't seem to get it right!
I got to a list with this query:
SELECT course, count( * ) AS cnt FROM uniresults WHERE symbol = 'F' GROUP BY course
Now somehow I need to use that, to realise that 1 is the lowest count, and then print out the course codes which have lowestcount eg.
For each PHY2 student, show the number of senior courses they passed (pass if mark>= 50 and course is senior if seniorCourse is 'Y'.
I have realised that I need to find the stuID's of the PHY2 students, and then for each of those stuNums I need to count the senior courses passed.
I have been trying to play around with IN and HAVING, but I can't seem to relate the student number back to mark, and seniorcourse.