Sum of two marks divided by 2 for each student The image shown below is the answer I found, but I want the sum of two marks divided by 2. The image shows that we take only the better mark from task 1 and task 2. I want the better mark from task 1 and task 2 and add the "pen paper" mark divided by 2 for each student. My code is shown below:

WITH StudentMarks
AS (
SELECT L.NAME AS Class
    ,O.NAME AS Division
    ,P.NAME AS Subpart
    ,Q.NAME AS Term
    ,R.NAME AS Subject
    ,N.StudentID AS StudentID
    ,N.Mark AS Mark
    ,N.Grade AS Grade
    ,J.NAME AS Assesmenttool
    ,K.NAME AS Studentname
FROM MarkEntryMaster M
INNER JOIN MarkEntryDetail N ON M.ID = N.MEMasterID
INNER JOIN Assesment J ON M.AssessmentToolID = J.ID
INNER JOIN AdmissionRegister K ON N.AdmissionNoID = K.ID
INNER JOIN Course L ON M.CourseID = L.ID
INNER JOIN Division O ON M.DivisionID = O.ID
INNER JOIN SubPart P ON M.SubPartID = P.ID
INNER JOIN SchoolTerm Q ON M.TermID = Q.ID
INNER JOIN Subject R ON M.SubjectID = R.ID
WHERE  m.AssessmentToolID IN ('3','4')
)
SELECT Class
,Division
,Subpart
,Term
,Subject
,StudentID
,Mark
,Grade
,Assesmenttool
,Studentname
FROM StudentMarks a
WHERE Mark = (
    SELECT max(Mark)
    FROM StudentMarks
    WHERE Class = a.Class
        AND Division = a.Division
        AND Subpart = a.Subpart
        AND Term = a.Term
        AND Subject = a.Subject
        AND StudentID = a.Studentid

    )

union 

SELECT L.NAME AS Class
    ,O.NAME AS Division
    ,P.NAME AS Subpart
    ,Q.NAME AS Term
    ,R.NAME AS Subject
    ,N.StudentID AS StudentID
    ,N.Mark AS Mark
    ,N.Grade AS Grade
    ,J.NAME AS Assesmenttool
    ,K.NAME AS Studentname
FROM MarkEntryMaster M
INNER JOIN MarkEntryDetail N ON M.ID = N.MEMasterID
INNER JOIN Assesment J ON M.AssessmentToolID = J.ID
INNER JOIN AdmissionRegister K ON N.AdmissionNoID = K.ID
INNER JOIN Course L ON M.CourseID = L.ID
INNER JOIN Division O ON M.DivisionID = O.ID
INNER JOIN SubPart P ON M.SubPartID = P.ID
INNER JOIN SchoolTerm Q ON M.TermID = Q.ID
INNER JOIN Subject R ON M.SubjectID = R.ID
where (M.AssessmentToolID=5)
"I want the better mark from task 1 and task 2 and add the "pen paper" mark divided by 2 for each student."

Let's work backwards from the answer that you want. Suppose that you had a derived table or CTE with the following columns:

StudentName  
Task1Mark  
Task2Mark  
PenPaperMark  


With that data structure it would be fairly straightforward to calculate what you need, right? Something like this:

SELECT
  StudentName
, 0.5 * (PenPaperMark + CASE WHEN Task1Mark &gt;= Task2Mark THEN Task1Mark ELSE Task2Mark END)
FROM
(
...
) t;


You may need to account for NULL values or do some kind of rounding, but if you have the columns that I listed before you can perform the necessary calculation. So how can you get one row per student with the column values that you need?

It looks like your source data has one row per student and per mark. You need a way to combine the multiple rows from each student into one summarized row per student. PIVOT can turn rows into columns. You can also code it as a GROUP BY query. Since you want one row per student you would group by that column. So something like this could work for a definition of t:

SELECT 
StudentName
, MAX(CASE WHEN AssesmentTool = 'Task 1' THEN Mark ELSE NULL END) Task1Mark  
, MAX(CASE WHEN AssesmentTool = 'Task 2' THEN Mark ELSE NULL END) Task2Mark  
, MAX(CASE WHEN AssesmentTool = 'Pen Papper Test 1' THEN Mark ELSE NULL END) PenPaperMark    
FROM
...
GROUP BY StudentName


All that remains is to complete the FROM clause with the appropriate tables from your data model.