0

I have a DB with two tables
schoolresults;
uniresults;

Extracts are as follows:

schoolresults
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)

uniresults:
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

course cnt
ACCS1 2
ACCS2 1
ACCS3 1
BUS1 3
CS1 1
CS3 1
ECO1 1
GAM2 2
MAM1 3
MAM2 1
MAM3 3
PSY1 1
STA2 2
STA3 1

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.

ACCS2 1
ACCS3 1
CS1 1
CS3 1
ECO1 1
MAM2 1
PSY1 1
STA3 1

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.

Please help!
Regards,
John

2
Contributors
4
Replies
5
Views
7 Years
Discussion Span
Last Post by d5e5
0

For your first question, try this (not tested)

SELECT course, cnt
FROM (
SELECT course, count( * ) AS cnt
FROM uniresults
WHERE symbol = 'F'
GROUP BY course) `b`
WHERE cnt = (
SELECT min( cnt )
FROM `course_count`
)
0

I actually got it working with code that is quite similar (nested select statements with grouping and counting). Although I don't think the line 'course_code' would work in your query.

0

I actually got it working with code that is quite similar (nested select statements with grouping and counting). Although I don't think the line 'course_code' would work in your query.

Oops. You are correct. I had created a table called 'course_code' with your intermediate results as a short cut while I was experimenting. Anyway, I'm glad you came up with something that works.

Edited by d5e5: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.