I am having trouble with the below, I need to average two columns in a correlated subquery and I cannot get it to work, how do i get both aggregate sub queries to run then go through the outer query? I am not even sure I am on the right path so any guidance appreciated.
Question: Display the ID, Name and average Exam and Coursework weights for all lecturers and make sure all the columns have suitable headings, using a correlated query. Investigate use of the ISNULL function to produce suitable output in the case of a lecturer not teaching any modules.
SELECT l.StaffID, l.FirstName, l.Surname, m.ModuleDescription FROM dbo.Lecturer as l RIGHT OUTER JOIN dbo.Module as m ON l.StaffID = m.ModuleConvenor WHERE m.ExamWeight = SELECT AVG(m.ExamWeight) AS 'Avg ExWeight' FROM dbo.Module as m2 WHERE m2.ModuleConcenor = l.StaffID OR m2.ModuleConvenor IS NULL) WHERE (SELECT AVG(m.CWWeight) AS 'Avg CWWeight' FROM dbo.Module as m3 WHERE m3.ModuleConcenor = l.StaffID OR m3.ModuleConvenor IS NULL)