Sql-server – Two aggregated sub queries wont run

aggregatesql serversubquery

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)

Best Answer

Try this, it should help put you in the right direction.

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(m2.ExamWeight) AS 'Avg ExWeight'
              FROM dbo.Module as m2
             WHERE m2.ModuleConcenor = l.StaffID OR m2.ModuleConvenor IS NULL) 
 AND m.CWWeight =  (
    SELECT AVG(m3.CWWeight) AS 'Avg CWWeight'
     FROM dbo.Module as m3
    WHERE m3.ModuleConcenor = l.StaffID OR m3.ModuleConvenor IS NULL)

I did the following

  • Updated your where clause syntax
  • Update your aggregate function typo from AVE to AVG
  • Updated your alias being used in the aggregate function in both subqueries

You may need to post some sample data and table ddl information. It is not clear to me how the module and lecturer tables should be joined based on your query. This answer helps correct your syntax, it may not be full solution to what you are trying to achieve.

Related Question