Member Avatar for John Linux

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
  • 164 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by d5e5
Member Avatar for John Linux

I managed to solve the 2nd question, will post the code.

Member Avatar for d5e5

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`
)
Member Avatar for John Linux

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.

Member Avatar for d5e5

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 because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.