Hi, @6odfrey
Do you want to know the query?
Try the following: However, I don't have any environment or test data so this query isn't working. I just assembled it on my desk.
If you get an error, try fixing it.
1)
select
Count(t2.SubjectCode)
from
Students t1
inner join Enrolments t2
on t1.Ref = t2.StudentRef
where
t1.Ref = 20151021
2)
select
*
from
Subjects t1
inner join Enrolments t2
on t1.Code = t2.SubjectCode
inner join Students t3
on t2.StudenttRef = t3.Ref
where
t1.Code = 'A17cmp'
order by
t3.Surname, t3.Forename
3)
select
t3.Description,
t4.stucnt
from
Students t1
inner join Enrolments t2
on t1.Ref = t2.StudentRef
inner join Subjects t3
on t2.SubjectCode = t3.Code
inner join
(
select
SubjectCode as subcode,
Count(StudentRef) as stucnt
from
Enrolments
group by SubjectCode
) t4
on t3.Code = t4.subcode
where
t1.Surname = 'Smith'
order by
t4.stucnt