How to get the average of a nested query returned as a single result based on the parent query

Here is another problem I have had to deal with in the last couple of days. I have the following query:

SELECT S.Name,S.Surname,S.Student_ID,S.StudentNumber,C.Course, B.Campus_Title,M.Module,SM.Percentage_Obtained,S.Days_Absent
FROM Students S 
INNER JOIN Student_Courses SC
ON SC.StudentID = S.ID 
INNER JOIN Courses_Te开发者_开发百科mplate C 
ON C.ID = SC.courseID
INNER JOIN Branches B
ON B.ID = S.BranchID 
INNER JOIN Student_Modules SM ON
SM.StudentID =S.ID
INNER JOIN Modules_Template M ON
M.ID = SM.ModuleID

The problem is that I want to rewrite the query to get a calculated result like this:

SELECT S.Name,S.Surname,S.Student_ID,S.StudentNumber,C.Course, B.Campus_Title,M.Module,SM.Percentage_Obtained,S.Days_Absent,
       (SELECT AVG(Percentage_Obtained) 
        FROM Student_Modules 
        INNER JOIN Courses_Template 
        ON Courses_Template.ID = Student_Modules.CourseID
        INNER JOIN Modules_Template 
        ON Courses_Template.ID = Modules_Template.CourseID
        WHERE Modules_Template.Module= M.Module)[AS Class Average]
FROM Students S 
INNER JOIN Student_Courses SC
ON SC.StudentID = S.ID 
INNER JOIN Courses_Template C 
ON C.ID = SC.courseID
INNER JOIN Branches B
ON B.ID = S.BranchID 
INNER JOIN Student_Modules SM ON
SM.StudentID =S.ID
INNER JOIN Modules_Template M ON
M.ID = SM.ModuleID

The Original Values for the modules is (extracted)

M.Modules   AVG(Percentage_Obtained)

Module 1    99
Module 2    98
Module 3    94
Module 1    94
Module 2    22
Module 3    100

The expected Results that I wanted from the second query was this

M.Modules   AVG(Percentage_Obtained) (SubQuery's Average)
Module 1    99                          97  
Module 2    98                          60
Module 3    94                          96
Module 1    94                          97
Module 2    22                          60
Module 3    100                         96

however, what it gave to me was something completely different

The expected Results that I wanted from the second query was this

M.Modules   AVG(Percentage_Obtained) (SubQuery's Average)
Module 1    99                          84  
Module 2    98                          84
Module 3    94                          84
Module 1    94                          84
Module 2    22                          84
Module 3    100                         84

Can anyone perhaps spot my mistake from somewhere? I need all of these fields in a single query, because I must make a report on it. The sub query's results is crucial

Its almost like I need this (but Dynamically for All recurrences of a Module):

SELECT AVG(Percentage_Obtained) 
FROM Student_Modules 
INNER JOIN Modules_Template ON Modules_Template.ID = Student_Modules.ModuleID 
WHERE Modules_Template.Module = 'Module 1' -- And module 2, and module 3 .... but Dynamically

---

You can try the following query:

SELECT S.Name,S.Surname,S.Student_ID,S.StudentNumber,C.Course, B.Campus_Title,M.Module,SM.Percentage_Obtained,S.Days_Absent, SM2.AvgPercentage_Obtained
FROM Students S 
INNER JOIN Student_Courses SC
ON SC.StudentID = S.ID 
INNER JOIN Courses_Template C 
ON C.ID = SC.courseID
INNER JOIN Branches B
ON B.ID = S.BranchID 
INNER JOIN Student_Modules SM ON
SM.StudentID =S.ID
INNER JOIN Modules_Template M ON
M.ID = SM.ModuleID
OUTER APPLY (SELECT AVG(Percentage_Obtained) AvgPercentage_Obtained
             FROM Student_Modules 
             INNER JOIN Modules_Template ON Modules_Template.ID = Student_Modules.ModuleID
             WHERE Student_Modules.Module = M.Module) SM2