You say you have tried this. I assume this is an attempt at the syntax (rather than it returning incorrect results) as there are errors:
1. Enrollement is not a table name - should be Enrollment
2. Program_Year_When_Enorolled - this is a misspelling isn't it?
3. avg_third_year_marks has been specified as an output column twice
4. Course_Current_Year is not a column in the Students table
Can you rectify and resumbit please. Meanwhile I'll see if I can make it work using some assumptions.
Here's a solution if I understood your problem correctly:
SELECT *,
Averages.avg_third_year_marks*2/3 + Averages.avg_second_year_marks*1/3 AS overall
FROM
(
SELECT
Student_ID,
(
SELECT AVG(Marks_obtained)
FROM Students s1
LEFT JOIN Enrollment e1 ON s1.Student_ID=e1.Student_ID
WHERE s1.Student_ID=s.Student_ID
AND Program_Year_When_Enrolled = 'Third'
) AS avg_third_year_marks,
(
SELECT AVG(Marks_obtained)
FROM Students s2
LEFT JOIN Enrollment e2 ON s2.Student_ID=e2.Student_ID
WHERE s2.Student_ID=s.Student_ID
AND Program_Year_When_Enrolled = 'Second'
) AS avg_second_year_marks
FROM
Students s
) AS Averages
"Yes thank you very much this works perfectly. However, the results should not be shown for all students, they should relate only to those students whose Course_Current_Year in the students table is 'Graduated-2017'. Can you please tell me how to place this additional restriction. Thanks again"
I've rearranged the post as I was replying incorrectly and I've now lost your reply. Sorry about that...
As I said in my original post there is no column
Students.Course_Current_Year so I'm not sure how you can achieve that. Let me know if you need to correct your table definition first and we'll go from there.